Tableau - How to Keep Only the Historical Trends of the Highest Current Values

Imagine you have a busy worksheet in Tableau that looks like this:

54-1.png

Each line represents a single facility and displays that facility’s overtime hours. Imagine you want to filter to only keep the trends for the 3 facilities with the highest overtime hours from the most recent date BUT you also want that filter to be dynamic so when you update the data there might be a new top 3.

Initially you might create a calculated field to just keep the overtime hours figure if it was ranked in the top 3:

54-2.png

I used that Top 3 field as a filter and only kept values where the statement evaluated “True”. I also applied the tableau calculation using Specific Dimensions so there was a unique rank for each date.

54-3.png

Unfortunately, that approach doesn’t work. It keeps the top 3 facilities for each date, but that top 3 changes from one date to the next so a total of 11 facilities are still represented.

54-4.png

What we need to do is create a calculation that ranks a facility 1-12 throughout its history based on its current value. That’s going to take a bit more work.

First, I’ll create a calculated field that returns the overtime hours from the most recent date for a facility all the way back to the first date.

54-5.png

There’s a lot happening in this calculation. We are using a LOD expression to only keep data from the most recent date. Then, we are fixing at the facility level (but not date) so that the value from the max date is applied to every row for an individual facility.

Here’s what it looks like broken out:

54-6.png

We can drop Overtime Hours - Max Date into the Marks Card on the worksheet and apply a rank using specific dimensions.

54-7.png

This is what it looks like on “Label”:

54-8.png

We can move the field to filters and just keep 1-3:

54-9.png

And there you have it:

54-10.png

Want to learn more about how to get the most out of Tableau Calculations? Check out our Mastering Tableau Calculations Workshop.