Tableau - Calculated field for difference between date and maximum date in table

Question

I have the following table that I have loaded in Tableau (It has only one column CreatedOnDate)

+-----------------+
| CreatedOnDate   |
+-----------------+
| 1/1/2016        |
| 1/2/2016        |
| 1/3/2016        |
| 1/4/2016        |
| 1/5/2016        |
| 1/6/2016        |
| 1/7/2016        |
| 1/8/2016        |
| 1/9/2016        |
| 1/10/2016       |
| 1/11/2016       |
| 1/12/2016       |
| 1/13/2016       |
| 1/14/2016       |
+-----------------+

I want to be able to find the maximum date in the table, compare it with every date in the table and get the difference in days. For the above table, the maximum date in table is 1/14/2016. Every date is compared to 1/14/2016 to find the difference.

Expected Output

+-----------------+------------+
| CreatedOnDate   | Difference |
+-----------------+------------+
| 1/1/2016        |         13 |
| 1/2/2016        |         12 |
| 1/3/2016        |         11 |
| 1/4/2016        |         10 |
| 1/5/2016        |          9 |
| 1/6/2016        |          8 |
| 1/7/2016        |          7 |
| 1/8/2016        |          6 |
| 1/9/2016        |          5 |
| 1/10/2016       |          4 |
| 1/11/2016       |          3 |
| 1/12/2016       |          2 |
| 1/13/2016       |          1 |
| 1/14/2016       |          0 |
+-----------------+------------+

My goal is to create this Difference calculated field. I am struggling to find a way to do this using DATEDIFF.

And help would be appreciated!!

Petr Havlik Petr Havlik · Accepted Answer · 2016-10-07T08:11:47

woodhead92, this approach would work, but means you have to use table calculations. Much more flexible approach (available since v8) is Level of Details expressions:

First, define a MAX date for the whole dataset with this calculated field called MaxDate LOD:

{FIXED : MAX(CreatedOnDate) }

This will always calculate the maximum date on table (will overwrite filters as well, if you need to reflect them, make sure you add them to context.

Then you can use pretty much the same calculated field, but no need for ATTR or Table Calculations:

DATEDIFF('day', [CreatedOnDate], [MaxDate LOD])

Hope this helps!

Tableau - Calculated field for difference between date and maximum date in table

1 Answers