4 Replies Latest reply on Dec 21, 2016 11:42 AM by Dan Hong

# How can I calculate difference between dates in the same column but different categories?

I'm sure someone will point out that this is a basic reshaping problem, but I guess I'm poor at Googling, so I'm offering my question up to the gods of Stack Overflow.

My problem is that I want to calculate the number of days between two dates (of Rank 1 and 2) that come from the same column but have different values in another column (Rank). Here's an example of the dataset schema:

User   Date              Rank

Bob    2016-12-01      3

Bob    2016-12-07      2

Bob    2016-12-10      1

What I would like is this:

User   Date1            Date2           DaysBetween

Bob    2016-12-07   2016-12-10            3

Ideally, I want to do this in Tableau, but SQL/BigQuery is OK, too.

• ###### 1. Re: How can I calculate difference between dates in the same column but different categories?

Hello Dan,

What version of Tableau are you working with and do you have a sample workbook to provide a quick starting ground for the community to assist?

There is also faq on the subject of FAQ:  Open & Close Dates  that you may find as a great starting place.

Thanks

Patrick

• ###### 2. Re: How can I calculate difference between dates in the same column but different categories?

Actually, now I want to try something different. Given a list of users and different dates, ranked in order of recency (most recent date ranked 1), I'd like to be able to get the average difference between adjacent dates per user. So, using the top table as an example:

User    Start Date    End Date    Days Between

Bob      2016-12-01  2016-12-07            6

Bob      2016-12-07  2016-12-10            3

Which I could collapse by user and get Bob's average days as 4.5.

• ###### 3. Re: How can I calculate difference between dates in the same column but different categories?

Dan,

To get what you're looking for you'll need to create a calculation that looks like this:

{FIXED [User] : (DATEDIFF('day', Min([Logdate]), max([Logdate]))) / (COUNTD([Logdate])-1)}

This is taking the difference between the max and min dates for each user and dividing by the number of dates in the set. This should get you the average you're looking for.

This is what the table would look like:

-Wesley

1 of 1 people found this helpful
• ###### 4. Re: How can I calculate difference between dates in the same column but different categories?

Ahhh! That's how FIXED is used! This is perfect. Thanks, Wesley!