Crystal Enterprise 9: How to Calculate average for datediff

building reports crystal reports
1

So I have this statement that calculates the time difference. It uses the previous mod_date minus the current mod_date. Now I would like to get the average of each users time interval, basically average of DateDiff.

Below is the SQL to get the time interval:

(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

I’ve tried something like:

Select AVG(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

But I keep getting prompted for some numerical value on AVG().

If I type out Average instead of AVG for this statement:

Select Average(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

I get, “A field is required here” message, any ideas??

leke (BOB member since 2014-08-04)

2

I know that Crystal can be picky about what you can put into some of it’s formulas. You may need to create an manual average by using global/shared variables.

kevlray :us: (BOB member since 2010-06-23)

3

Not quite certain what you mean. Can you post an example?

leke (BOB member since 2014-08-04)

4

Make a formula (I am assuming) to be put in the details.

shared numbervar totaldatediff;

totaldatediff := totaldatediff + (DateDiff (“s”,previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

Also make a running total to do the record count or if it works for you just use the RecordNumber function.

So in the Report Footer (again making a assumption on where you want your average). Make another formula

shared numbervar totaldatediff;
totaldatediff/RecordNumber

Hopefully this will get you started.

kevlray :us: (BOB member since 2010-06-23)

5

So this formula field

(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60

is suppose to calculate interval times., and works great as is. All I want is the average of those interval times. I want to be able to then remove the prompts for userID and show top average of all users based on their interval times.

leke (BOB member since 2014-08-04)

6

If you want to show a top average for all employees, I think it will get a bit more complicated.

I am assuming that you will be grouping by userid. So I think you would want two shared variables (one for the userID and the other for the average calculation). You would initially set the variable for the average calculation to zero in a formula in the report header. Put the first formula I gave you in the details. The second formula would be replace with something like this and put in the group footer.

shared numbervar topdatediff;
shared numbervar totaldatediff;
shared stringvar topuserid;

if totaldatediff > topdatediff then
(
topdatediff := totaldatediff;
topuserid := userid
)

Then where ever you want to display the results just have a formula with each of the shared variables (i.e., shared numbervar topdatediff)

I do not know what you want to do with ties.

Hopefully this will work for you. FYI: If anyone else has a better idea, please chime in.

kevlray :us: (BOB member since 2010-06-23)

7

I have a Formula field called

lapsed time

that calculates the datediff with the date diff code. What if I perform summary functions specific to the grouping?

Local NumberVar TotalSec := Average({@lapsed time},{PROD_TRKG_TRAN.MOD_DATE_TIME});
Local NumberVar Days     := Truncate (TotalSec / 86400); 
Local NumberVar Hours   :=  Truncate (Remainder ( TotalSec,86400) / 3600); 
Local NumberVar Minutes :=  Truncate (Remainder ( TotalSec,3600) / 60); 
Local NumberVar Seconds :=  Remainder ( TotalSec , 60); 
 
Totext ( Days,   '00', 0,') + ':'+ 
Totext ( Hours,  '00', 0,') + ':'+ 
Totext ( Minutes,'00', 0,') + ':'+ 
Totext ( Seconds,'00', 0,'

Am I on the right track? I do get “The ) is missing”.

leke (BOB member since 2014-08-04)

8

Duh! Of course you could do the AVG by group. Not sure how you want to ID the top average.

On the formula listed, you are missing a paren on the last Totext.

kevlray :us: (BOB member since 2010-06-23)

9

Still getting same message, after adding the paren to the last totext:

Local NumberVar TotalSec := Average({@lapsed time},{PROD_TRKG_TRAN.MOD_DATE_TIME});
Local NumberVar Days := Truncate (TotalSec / 86400);
Local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, ‘00’, 0,‘) + ‘:’+
Totext ( Hours, ‘00’, 0,’) + ‘:’+
Totext ( Minutes,‘00’, 0,‘) + ‘:’+
Totext ( Seconds,‘00’, 0,’)

leke (BOB member since 2014-08-04)

10

I was looking closer at your Totext functions and I think you are missing a tick mark for each of your Totext functions.

Totext ( Days, ‘00’, 0,’’) + ‘:’+
Totext ( Hours, ‘00’, 0,’’) + ‘:’+
Totext ( Minutes,‘00’, 0,’’) + ‘:’+
Totext ( Seconds,‘00’, 0,’’)

kevlray :us: (BOB member since 2010-06-23)

11

Made the changes and this is what I get:

leke (BOB member since 2014-08-04)

12

Are doing a summary in that formula? If so that is why you are getting the message.

kevlray :us: (BOB member since 2010-06-23)

13

I’m leaning towards creating a running total not my forte. Create a running totals of the time then calculate average of that. I initially wanted a way to create a formula that calculated the average of the datediff.

leke (BOB member since 2014-08-04)