[RESOLVED] DateDiff - First Record

[jre1229]

Hi,
I am using a datediff formula to tell the number of days between records. The problem I am running into is that on the first row of each group, it looks at the last row of the previous group to tally the difference. I am trying to set the field equal to zero for the first row of each group. Does anyone know how to tell if the row is the first one in the group?
thanks!

[jggtz]

You could Insert a Running Total field that Counts the records and Reset on change of Group

[brucevde]

Assuming Crystal Reports...

Use the Previous function to see if the previous record and the current record are in the same group.

//very first record in the report should always return 0
If PreviousIsNull ({Customers.CustomerID}) Then
  0
Else
  //only calculate days between orders after the first record in the CustomerId Group
 //formula returns 0 if the following is false (could also use an Else clause).
  If Previous ({Customers.CustomerID}) = {Customers.CustomerID} Then
     DateDiff ("d", Previous ({Orders.OrderDate}) ,{Orders.OrderDate})

[jre1229]

jggtz, can you explain this a little more. Would I have one variable (Variable1) and set it := 0 then have another (Variable2) and set it := Variable1 +1 ????

You could Insert a Running Total field that Counts the records and Reset on change of Group

[jre1229]

This works great Brucevde! Thanks! I worked on that crude for two hours yesterday! Thanks again!

Assuming Crystal Reports...

Use the Previous function to see if the previous record and the current record are in the same group.

//very first record in the report should always return 0
If PreviousIsNull ({Customers.CustomerID}) Then
  0
Else
  //only calculate days between orders after the first record in the CustomerId Group
 //formula returns 0 if the following is false (could also use an Else clause).
  If Previous ({Customers.CustomerID}) = {Customers.CustomerID} Then
     DateDiff ("d", Previous ({Orders.OrderDate}) ,{Orders.OrderDate})

[jre1229]

I do seem to have another problem now though. If you can't answer it I will just re-post under a new title. I just figured I would try it here first. It figures up my datediff for my records. Now I need to sum all the datediffs but when I try Sum({DateDiffFormula}) it says that "this field cannot be summarized", how can I get the total number of days in the group?

[brucevde]

It looks like you cannot use Sum(@formula) if the formula uses any of the PrintState functions. I could not create a RunningTotal field on that formula either.

The only way I could generate a sum was by using a Global Variable.

Create 3 additional formulas

1) ResetTotalDays - place this formula in the Group Header and suppress it.
WhilePrintingRecords;
Global numberVar TotalDays:=0;
TotalDays;

2) SetTotalDays - place in the Details section also suppressed.
WhilePrintingRecords;
Global numberVar TotalDays;
TotalDays:= TotalDays+{@DaysBetweenOrders};

3)DisplayTotalDays - place in the Group Footer section.
WhilePrintingRecords;
Global numberVar TotalDays;
TotalDays;

[jre1229]

Worked great bruce! Thanks!