Imagine you have a busy worksheet in Tableau that looks like this:
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:
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.
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.
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.
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:
We can drop Overtime Hours - Max Date into the Marks Card on the worksheet and apply a rank using specific dimensions.
This is what it looks like on “Label”:
We can move the field to filters and just keep 1-3:
And there you have it:
Want to learn more about how to get the most out of Tableau Calculations? Check out our Mastering Tableau Calculations Workshop.