11 Replies Latest reply on Jun 4, 2015 12:55 PM by Cyril Belmehdi

# Number of days between a ship date on row #1 and the next order date on row #2

Using the Superstore Sales data set, I'm attempting to determine the number of days, by customer, between a ship date and the immediate next order date and have these two dates be on the same line, if possible. For example, Aaron Bergman's first order shipped on February 25, 2011, and his next order date was 10 days later on March 7, 2011.

The ideal row setup would look like this:

Previous Order Ship DateCurrent Order DateDays to Order
February 25, 2011March 7, 201110

I searched the forums and really wanted to find the answer here FAQ:  Open & Close Dates but without success. Any help would be greatly appreciated. Thank you!
• ###### 1. Re: Number of days between a ship date on row #1 and the next order date on row #2

Date difference in Days, Month and Year

You have to use this formula:

IF DATEPART('day',[Order Date])-DATEPART('day',[Ship Date]) <0

THEN (DATEPART('day',[Order Date])-DATEPART('day',[Ship Date]))*-1

ELSE DATEPART('day',[Order Date])-DATEPART('day',[Ship Date]) END

Hope this helps!

Sabine O.

• ###### 2. Re: Number of days between a ship date on row #1 and the next order date on row #2

What you need is to play with the lookup function. Lookup allows you to get another row value than the current.

So lookup(ATTR([Order Date]),1) is the order date of the next row

ATTR([Order Date]) Is the date of the current row.

Your difference is (ZN is to replace null by 0 for the last row):

ZN(DATEDIFF("day", ATTR([Order Date]), lookup(ATTR([Order Date]),1)))

Kind regards

• ###### 3. Re: Number of days between a ship date on row #1 and the next order date on row #2

Thank you, Sabine, for your reply. It seems as though this will work for date fields in the same row. As the dates I am comparing are in different rows, it doesn't appear that this calculation will produce the desired outcome. (I did test this solution, and it did not produce the outcome.) Thank you just the same.

• ###### 4. Re: Number of days between a ship date on row #1 and the next order date on row #2

Hi, Cyril.

Thank you for your reply. Here are the results I have gotten thus far.

The calculation seems to be producing some odd results, and it does not restart for each customer; it just continues until the end of the column. For example, for the difference between Aaron Bergman's first ship date of February 25, 2011 and next order date of March 7, 2011, the difference is 11 days, but the calculation provides 16 days. However, for the following ship date of March 8, 2011 and the next order date of November 11, 2013 the calculation provides 980 days, which is the correct answer. Not sure what exactly is happening here.

• ###### 5. Re: Number of days between a ship date on row #1 and the next order date on row #2

Could you send a workbook, practice is much better than theory

You just need to player with the table calculation compute mode. In other words, you need to explain where to restart the calc

• ###### 6. Re: Number of days between a ship date on row #1 and the next order date on row #2

Just by quickly looking at Cyril's calc. It seems like the first field in the datediff calc needs to be ship date not order date. So you look at the ship date of the current row and take the order date of the next row by using the lookup function. Ofcourse, you will need to change the compute using settings to make the last row 0 for every customer because there is no order date.

1 of 1 people found this helpful
• ###### 7. Re: Number of days between a ship date on row #1 and the next order date on row #2

Yes, thanks, I didn't noticed for the ship date.

I'm not abe to compute the right way, it's not working. So I have a workaround for you :

// If my client on the next row is the same than the current one then next order date minus ship date

// Else no calculation so 0

IF LOOKUP(ATTR([Customer Name]),1) = ATTR([Customer Name]) Then

ZN(DATEDIFF("day", ATTR([Ship Date]), lookup(ATTR([Order Date]),1)))

Else 0

END

• ###### 8. Re: Number of days between a ship date on row #1 and the next order date on row #2

Thank you, Poojah! This is certainly a helpful edit to the suggestion that Cyril gave. Do you have an idea as to how it would be possible to restart the calculation for each customer? If I compute using customer name, it gives me an odd and incorrect result.

• ###### 9. Re: Number of days between a ship date on row #1 and the next order date on row #2

Look my previous post I've had the same issue than you but there is a workaround

• ###### 10. Re: Number of days between a ship date on row #1 and the next order date on row #2

That works! Thanks, Cyril, for the workaround and for all of your assistance with this. I really appreciate it.

Joel

• ###### 11. Re: Number of days between a ship date on row #1 and the next order date on row #2

You're welcome

I forget to mention that you said there's 980 days between March 8, 2011 and the next order date of November 11, 2013..If it's what you want  then do a + 1 to my calc.

The reason is that datediff between today and the day after tomorrow is 2 but you want 3 and it's normal. Dateddif is a interval it's calculated based on 00:00 hour. Which means that between today 00:00 and the day after tomorrow at 00:00 there's 48h which is 2 days.

Not sure I'm clear but it's normal to do plus 1 when you do date diff without the timestamp.

Kind regards