-
1. Re: finding number of days between two dates
Joshua Milligan Jan 10, 2013 1:57 PM (in response to SUK Rudge)SUK,
I think the answer is a calculated field using DATEDIFF and a lookup table calculation.
The calculation might look like this:
DateDiff('day', Lookup(ATTR(Date), 0), Lookup(ATTR([Date]), 1))
and the results are attached. Hopefully that gives you a good starting place. If you want to attach your data that might help too.
Joshua
-
DateDiff with Lookup.twbx.zip 16.7 KB
-
-
2. Re: finding number of days between two dates
Jonathan DrummeyJan 10, 2013 3:21 PM (in response to Joshua Milligan)
Hi Joshua,
For performance, the first LOOKUP() is not necessary - LOOKUP(ATTR([Date]),0) will return the same results as ATTR([Date]) - in this example at least. If the dates don't include times, I suspect that LOOKUP(ATTR([Date]),1)-ATTR([Date]) might be even faster, since it's just subtracting the internal decimal days representation of dates.
Jonathan
-
3. Re: finding number of days between two dates
Joshua Milligan Jan 10, 2013 3:41 PM (in response to Jonathan Drummey)Jonathan,
Thanks! What's an example of a case where ATTR([Date]) would not be the same as LOOKUP(ATTR([Date]), 0)?
Joshua
-
4. Re: finding number of days between two dates
Jonathan DrummeyJan 10, 2013 4:12 PM (in response to Joshua Milligan)
Ok, that was a productive bit of time... The comment I made was a hedge, because I'm still learning the ins and outs of date padding and domain completion, and I wasn't sure what ATTR() did in all of those situations. I just set up a bunch of tests of date padding and domain completion and didn't see any cases where the LOOKUP(ATTR(),0) returned a different value than ATTR(). Thanks for asking!
-
5. Re: finding number of days between two dates
Joshua Milligan Jan 10, 2013 6:33 PM (in response to Jonathan Drummey)Jonathan,
So, what are some good resources for learning about domain padding in Tableau? I had a brief discussion with Joe Mako at TCC in which he asked me what I knew about it and I just stared blankly back at him. I still don't have a good grasp of the concept and I haven't found much on the subject.
Joshua
-
6. Re: finding number of days between two dates
Jonathan DrummeyJan 11, 2013 6:49 AM (in response to Joshua Milligan)
1) Joe Mako
2) [silence...]
A bit more seriously, my own knowledge is coming along and Richard Leeke also knows a fair bit, and it's very much practical knowledge found by trial, error (lots of error), and experimentation. Tableau staff like Ross Bunker and Jock Mackinlay know a lot because they designed and built it, and they're busy developing new features so we don't like to bother them.
Overall, Tableau calls this process "densification." Domain padding is a form of densification that Tableau uses with dates & bins, for example when you turn on the Show Missing Values option. Domain completion is what Tableau does when you have discrete pills on Rows and Columns with sparse data, Tableau pads out the data so there is a pane/cell for every combination of discrete values. Where this gets complicated is that domain completion and padding can look like each other, and there are times based on pill type, location, and mark type that densification can go away and/or come back. These factors are not documented and the set of factors and outcomes is still being discovered. Just last week Joe and I found a situation where Tableau started doing domain padding when neither of us had expected it to happen. We're trying to put together some more details on this, and it's a slow process.
As for why there isn't more documentation on this, I think it's kind of a scaffolding issue, that there haven't been enough people at Tableau or end users who have found use cases where densification is an issue (or not), grasp how Tableau works in this area, what the opportunities are, and the limitations. And I'm guessing that Tableau hasn't really wanted to document it because there are so many complexities, that they'd like to clean up the underlying functionality and make it adhere to more of the Tableau philosophy.
If you have specific questions or use cases, I'm happy to share what I know and I think I can say the same for Joe.
Jonathan
-
7. Re: finding number of days between two dates
SUK Rudge Jan 15, 2013 6:57 AM (in response to Joshua Milligan)Hi Joshua
Thank you for the immeadiate reply.i am actually looking for this kind of format just take a look at of my attachment.I have patient fall date and number of falls.now i have to map number of days between each fall using the fall date and no of falls on the chart to create G chart.
I want to know how to calculate no of days between each fall using the fall date?
Pls advice me in this.
-
G chart.docx 253.3 KB
-
-
8. Re: finding number of days between two dates
Jonathan DrummeyJan 15, 2013 7:39 AM (in response to SUK Rudge)
See the attached, I used INT() instead of datediff, but it gets the same results as Joshua's calc. Note that I fixed the Compute Using of the table calc to the Fall ID, that way if you drag another pill into the view (such as a floor/unit, to generate a small multiples chart) the calculation will occur separately for each unit. The average line was generated using a reference line.
Jonathan
-
days between fall.twbx.zip 39.3 KB
-
-
9. Re: finding number of days between two dates
SUK Rudge Jan 22, 2013 9:16 AM (in response to Jonathan Drummey)Thank you so much Joshua and Jonathan for helping me in this
-
10. Re: finding number of days between two dates
Jonathan DrummeyJan 22, 2013 9:45 AM (in response to SUK Rudge)
You're welcome!
-
11. Re: finding number of days between two dates
Pat Grady Feb 13, 2014 7:28 PM (in response to Joshua Milligan)This is wonderful, thanks for sharing!
Follow-up question:
Once you have a coumn full of the "difference of days from previous", what would be the best way to find the average of that column?
-
12. Re: finding number of days between two dates
Alex Cook Nov 6, 2014 9:14 AM (in response to SUK Rudge)Second follow-up question for the group:
I want to count the days between two dates, but I want the count to be based on the first opportunity for each account, and to reset with each new account - see attached. There can be multiple opportunities per account. I used Josh/Jonathan's suggestion to set up a Date Diff field, but it does not reset - it always just looks to the previous line to calculate the datediff (due to the "-1" in the lookup). I tried setting up a "Date of First Opp" field with the intent of just doing a datediff b/w this field and the Date field, but it is not dynamic. How do I get it to look back a dynamic number of lines? Anyone have any suggestions? This is easy to do in Excel but not so much in Tableau (or at least I don't know how to do it).
Thanks,
Alex
-
Date Diff, Take 2.twbx 32.5 KB
-
-
13. Re: finding number of days between two dates
Matt LuttonNov 6, 2014 9:36 AM (in response to Alex Cook)
-
14. Re: finding number of days between two dates
Alex Cook Nov 6, 2014 10:07 AM (in response to Matt Lutton)Thanks Matthew - Almost, but not quite. I figured the answer was somewhere
in the Table Calculation window...my mistake was I was only dragging
Account into the Addressing window so the "At the level" and "Restarting
every" options were not enabled. I needed to drag all the fields over to
the Addressing window. But, this is still incorrect since while the count
resets for every account, within each account it is still referencing the
prior opportunity. For example, for Account A, the correct datediff for Opp
3 would be 31 (the number of days b/w 1/1 and 1/31), not 17 as it's
currently showing.
I figured out the correct result using a slightly different method (see
Method 2 tab), but it would still be helpful to know how to do this using
my first method.
Alex
On Thu, Nov 6, 2014 at 11:37 AM, Matthew Lutton <
-
Date Diff, Take 2.twbx 33.2 KB
-