Sum a datediff field 2

[FireGeek21]

Is DateDiff considered an aggregrate function where you can't sum the DateDiff field? Trying to find a way to sum a number of datediff's per person (some may have one set of dates and others may have more). Need a grand total of days.

Ex:
Start Stop Days
Person 1 01/01/2010 01/15/2010 15
02/01/2010 02/20/2010 20

Person 2 01/01/2010 01/07/2010 7

Person 3 01/01/2010 01/05/2010 5
02/01/2010 02/28/2010 28
03/01/2010 03/03/2010 3
03/10/2010 366 (assume to current date)

On Person 1 looking for 35 in the footer, Person 2 should have 7 and Person 3 should have 402.

Thoughts?

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[briangriffin]

Did this not work for you? It works for me...

If it's not working, what messages, behavior, etc. are you seeing?

 

[FireGeek21]

Brian,

Here are the formulas I am using:

{@Days}
IF {@LOA Off} = Date(0, 0, 0)
THEN DateDiff("d", {@LOA On}, CurrentDate)
ELSE DateDiff("d", {@LOA On}, {@LOA Off})

{@LOA OFF}
IF {@LOA} = "ON" AND {PER_HX.EMPLOYEE} = Next({PER_HX.EMPLOYEE})
THEN Next({PER_HX.EFFDT})

{@LOA ON}
IF {@LOA} = "ON" THEN PER_HX.EFFDT}

If I right click on the {@Days} field, I cannot choose Insert to add a summary field. If I add a formula of SUM({@Days}) I get the message "This field cannot be summarized".

Maybe it is the Next() function in the {@LOA OFF} formula?



FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[raja2008]

Hello!

You can use a formula to get the sum

numbervar SumOfDays;
SumOfDays:=SumOfDays+datediff("d",start,stop);
SumOfdays;

Have this in the details sectoion and suppress it and then also place it in the Group footer for the sum to be displayed.


You also have to create another formula to reset the value of SumOfDays

ResetFormula

numbervar SumOfDays:=0;
and place it at the group header and suppress it.

 

[FireGeek21]

Raja,

I did try your attempt but it didn't work - well, how I had it written. In reading your suggestion, I noticed I missed something: SumOfDays:=SumOfDays+datediff("d",start,stop);

Thank you both!

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[raja2008]

I meant your start and stop dates .

Probably from your above post DateDiff("d", {@LOA On}, {@LOA Off})

Can you explain in more details,what exactly happened when you used the formula.

 

[FireGeek21]

Raja,

I was missing the SumOfDays + portion of the equation. My comment was acknowledging that. I do have it working thanks to you providing the whole equation.

Thanks again!

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)