CODEWITHSUNDEEP
datetableau-apidatediff
woodhead92
woodhead92

Reputation: 127

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

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!!

Upvotes: 0

Views: 5447

Answers (1)

Petr Havlik
Petr Havlik

Reputation: 3317

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!

Upvotes: 1

Related Questions