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

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

Jonathan,

Thanks!  What's an example of a case where ATTR([Date]) would not be the same as LOOKUP(ATTR([Date]), 0)?

Joshua

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!

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

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

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

You're welcome!

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?

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

Is this the result you're after?

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 <