Calculate Average of DateDiff

[brizaybrizoke]

Crystal 8.5

I need to calculate the average of a datediff and am not finding any previous posts with formulas that work with my report.

I currently have three formulas to achieve my datetime and one datediff formula converted to seconds.
The datediff is calculating the seconds between two transaction datetimes.
I also have one group that distinguishes between two types of orders.
Ideally, I would like to find the average for both types of orders.

Formula 1, Trans Date // date({TRANS_DATE})
Formula 2, Trans Time // time({TRANS_TIME})
Formula 3, Date Time // {@Trans Date} + {@Trans Time}
Formula 4, Date Diff // (datediff("s",(previous({@Date Time})),{@Date Time}))

When I attempt to create an average formula, Average // Average({@Date Diff}, GroupName({ORDER_TYPE})) // I receive a message stating 'The summary/running total field could not be created.'

Any help would be greatly appreciated! I've been scouring forums for two weeks to no avail! Thanks in advance!

 

[IanWaterman]

You can not use Previous() in a field you want to summarise.

You will need to use variables to capture datetime values and then sum them

@Init// place in group header and suppress so not visible
whileprintingrecords;
global numbervar datediff:=0;
global numbervar recno:=1;
global datetimevar prevdate:={@Date Time} // captures date of first record of group

@eval// place in detail and suppress so not visible
whileprintingrecords
global numbervar datediff;
global numbervar recno;
global datetimevar prevdate;
global datetimevar currdate:={@Date Time};

if recno > 1 then datediff:= datediff + datediff("s", prevdate, currdate);

recno:=recno+1

@display // group footer
whileprintingrecords
global numbervar datediff;
global numbervar recno;

datediff/(recno-1);

Ian

 

[brizaybrizoke]

Ian,

When attempting to create the first new formula 'Init' I receive an error message stating, 'A variable name is expected here' and the cursor falls in front of the word datediff.

Thoughts?

Thanks

 

[kray4660]

Ian: I have avoided using variable names that are reserved words for Crystal. Personally I think that the global variable datediff should be renames (dtdiff?) to avoid confusion.

 

[IanWaterman]

Good point Kray I was not testing in Formula editor and forgot about that.

Sorry brizaybrizoke if any of the variable names turn light blue in formula editor they are reserved names and should be avoided. Change slightly so that they stay black.

Ian

 

[brizaybrizoke]

Hi Ian,

I was able to successfully create all three new formulas (Init, Eval, Display)

After testing it against my data it appears that the result is less than it should be.

I've attached an example image of the report where blank spaces are suppressed data. Could the suppressed data be altering the results of the formulas?

Thank You

SUPPRESSED / {@Date Time} / {@Date Diff}

UNSUPPRESSED / {@Date Time} / {@Date Diff}

 

[IanWaterman]

The Init formula needs to be in the Group Header for A, B C etc.

If that is not possible try changing @Eval to

@eval// place in detail and suppress so not visible
whileprintingrecords
global numbervar datediff;
global numbervar recno;
global datetimevar prevdate;
global datetimevar currdate:={@Date Time};

if recno > 1 and {Whatever is ABC GroupField} <> Previous({Whatever is ABC GroupField})
then datediff:= datediff + datediff("s", prevdate, currdate);

recno:=recno+1;

Iamn