I believe the answer to your question is here:
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!
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)))
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.
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.
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
1 of 1 people found this helpful
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.
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)))
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.
Look my previous post I've had the same issue than you but there is a workaround
That works! Thanks, Cyril, for the workaround and for all of your assistance with this. I really appreciate it.
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.