1 of 1 people found this helpful
Create a calculated field : round(date field 1] - [date field 2], 0) if you're looking to calc. the difference in dates for a table of dates
The calculation that you are using (with DATEDIFF) should give you the total number of days. What does your view look like?
Hi James... just guessing here absent any more info about what your data or view looks like...
When you say "day fields" does that mean you have a different field for Month Day and Year?
Month Day Year 7 1 2012 9 2 2012
Date 7/1/2012 9/2/2012
If so, you can create a calculated field to turn your 3 fields into a date, then use the DATEDIFF function.
STR([Day]) // day#
STR([Year]) // year#
If you were doing datediff of 1 and 2, you would get 1 because 1 is 1/1/1900 and 2 is 1/2/1900...
If that's not the case, could you post more info about how your data or view looks?
I have a Problem with DATEDIFF function . I used it several time and gave me accurate numbers and now when I want try it in different worksheet it is not, even I tried a simple example, DATEDIFF('month', #July 15, 2004#, #April 15, 2004#), and still the same. Dont know what to do and need this function very bad
What result are you getting for:
DATEDIFF('month', #July 15, 2004#, #April 15, 2004#)
And, what result do you think you should be getting?
I am getting different results in different worksheets for example -1719. However, as you may know I should get the result of -3. I used this function a lot but I don't know what is wrong with it now.
I have noticed lately that Tableau gets confused sometimes. The weird part is that it keeps working but behaving strangely. For instance this morning I was toggling some one-click sorts on a 3-panel bar chart and at one point the bars all quit sorting, but the dimension (row label) would re-sort, so the bars and labels were completely out of sync. I solved my problem by closing and reopening the workbook. This was a long way of saying always try the old close/open trick first.
If you can post a sample workbook, I can take a look at it for you.
7 of 7 people found this helpful
You're using SUM(). You need to use a non-additive aggregation like MIN() MAX() AVG() or MEDIAN(). These will all return the -3 you are looking for.
Thanks Shawn . It resolved my problem
Very helpful, thank you Shawn.
Can you please provide the rationale?
It is not that tableau aggregates all the time periods per order id.