-
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.
-
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
-
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.
-
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.
-
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
-
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)1 of 1 people found this helpfulJust 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.
-
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
-
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.
-
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
-
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
-
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