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

Joel Hughes Jun 3, 2015 7:15 PM

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 Date	Current Order Date	Days to Order
February 25, 2011	March 7, 2011	10

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

Sabine Oussi Jun 4, 2015 12:53 AM (in response to Joel Hughes)

I believe the answer to your question is here:
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.
Like Show 1 Likes(1)

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

Cyril Belmehdi Jun 4, 2015 1:47 AM (in response to Joel Hughes)

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
Like Show 2 Likes(2)

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

Joel Hughes Jun 4, 2015 9:25 AM (in response to Sabine Oussi)

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.
Like Show 1 Likes(1)

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

Joel Hughes Jun 4, 2015 10:35 AM (in response to Cyril Belmehdi)

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.
Like Show 0 Likes(0)

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

Cyril Belmehdi Jun 4, 2015 11:06 AM (in response to Joel Hughes)

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
Like Show 1 Likes(1)

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

pooja.gandhi Jun 4, 2015 11:10 AM (in response to Joel Hughes)

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
Like Show 1 Likes(1)

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

Cyril Belmehdi Jun 4, 2015 12:00 PM (in response to pooja.gandhi)

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
Like Show 2 Likes(2)

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

Joel Hughes Jun 4, 2015 11:59 AM (in response to pooja.gandhi)

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.
Like Show 0 Likes(0)

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

Cyril Belmehdi Jun 4, 2015 12:01 PM (in response to Joel Hughes)

Look my previous post I've had the same issue than you but there is a workaround
Like Show 1 Likes(1)

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

Joel Hughes Jun 4, 2015 12:51 PM (in response to Cyril Belmehdi)

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

Joel
Like Show 0 Likes(0)

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

Cyril Belmehdi Jun 4, 2015 12:55 PM (in response to Cyril Belmehdi)

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
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

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

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

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

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

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

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

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

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

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

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

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

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

Actions

More Like This

Incoming Links