Days Calculation

[im4osu]

I have a report in which I need to calculate the number of days between a date value stored on each record for a customer. Below is an example of how the data would look:

order # customer ID order date
1 123 12/21/2004
5 123 12/28/2004
10 123 01/16/2005
25 123 02/15/2005

What I need is a calculation that would show something like this:

Customer 123
Order 1 12/21/2004
Order 5 12/28/2004 7 days
Order 10 01/16/2005 19 days
Order 25 02/15/2005 30 days

The calculation would need to reset itself at the beginning of each new customer group. I would greatly appreciate any assistance that you could provide!!

Sandy

 

[synapsevampire]

Create a formula such as the following for the details:

whileprintingrecords;
numbervar TheDays;
if not(onfirstrecord) then
TheDays:=TheDays+({table.date}-previous({table.date}))

Then in the Group header reset it using the following formula:
whileprintingrecords;
numbervar TheDays:=0;

-k

 

[im4osu]

I actually had a formula like this already. The calculation is great for just one customer but the first record of the next customer has the incorrect days. The way I see it, the first record of each customer should be 0 days since there is nothing to compare it to. Hoewver, the report is actually comparing the order date of the last record from the previous customer to the first order date for the current customer. This is causing my first record to have odd values like -512.

The only thing I can think of is that the TheDays value is not being reset properly. I have 2 groups for each customer due to some database issues. The first group is on customer ID and the next group is on order ID. I am resetting TheDays in the order ID header in order for the days to calculate propelry between orders.

Thanks again for your assistance!

 

[im4osu]

I figured it out. I just added a statement to the TheDays formula and it works fine. Here is what I am using:

whileprintingrecords;
numbervar TheDays;
if (not(onfirstrecord) and {table.custmerID} = previous({table.customerID})) then
TheDays:=TheDays+({table.orderdate}-previous({table.orderdate}))

Thanks for pushing me in the right direction!