Date Difference Totals

[hencyt]

Hello all. Not sure - this post might should go in the formula forum instead. I am working on a reorder rpt that lists invoices by customer and sorts by date. I put a counter field in that resets for each customer and basically assigns a sequential number to each invoice. I needed to show the number of days between seq invoices so we can figure out the frequency of their orders. That works really well. I basically have a datediff formula for each invoice that looks like this:
---------------------
if {invoicecounter} = 2 then datediff("d", {table.InvoiceDate}, previous({table.InvoiceDate}))
--------------------------
THe counter number changes from 2 up for each invoice. The problem is now, that I need to add up all of the date diff formulas per customer and divide by num of invoices to get an average per customer. No matter how I try to sum these date diff fields, crystal says that the summary couldn't be created. I tried using tonumber formula and that didn't work either.

I would appreciate any suggestions.
Thanks in advance!

Crystal 8.5

SunnyD

 

[synapsevampire]

I don't understand how "if {invoicecounter} = 2 then datediff("d", {table.InvoiceDate}, previous({table.InvoiceDate}))" would produce a counter any different than just using the datediff itself. And is invoicecounter a formula or a field from a table, parameter, or?

You can always add a variable to whatever formula you're using to keep track of a counter, such as:

whileprintingrecords;
numbervar MyCnt:=MyCnt+1

Then later reference it using:

whileprintingrecords;
numbervar MyCnt

-k

 

[hencyt]

k

Thanks for the reply. Maybe I made it harder than it needed to be, but I wanted to make sure the invoice differences were figured sequentially. I guess I don't understand how the system would know which 2 invoices you wanted to subtract unless you assigned them a number.
Maybe my post was unclear. Here is a sample of my report:

InvoiceA 01/01/06
InvoiceB 02/01/06 30 days
InvoiceC 02/24/06 24 days

I am needing to add up the date diff value. Here it is the 30 and the 24.

Does that help? I apologize if you answered my question already.

SunnyD

 

[lbass]

If you are just trying to allow for the fact that there won't be a previous record for the first record of a group, then {@datediff} should be:

if {#invoicecounter} >= 2 then datediff("d", {table.InvoiceDate}, previous({table.InvoiceDate}))

This assumes that {#invoicecounter} is an inserted running total that resets on change of group (customer).

To add {@datediff}, create three formulas:

//{@reset} to be placed in the customer group header:
whileprintingrecords;
numbervar diff := 0;

//{@accumdiff} to be placed in the detail section:
whileprintingrecords;
numbervar diff := diff + {@datediff};

//{@displayaverage} to be placed in the customer group footer:
whileprintingrecords;
numbervar diff;
diff/{#invoicecounter}

-LB

 

[lbass]

PS. For the sequencing, just sort on the date field.

-LB