If you have a data set that updates irregularly, figuring out how to filter to show only the latest data is difficult. Relative Date Filters are great but only work well if you have a set time you are filtering to like “today” or “yesterday”. If your latest data could be today, yesterday, or two days ago depending on the refresh schedule, things get trickier.
Imagine you work for an insurance company and have claim amount data refreshed semi-regularly. The data looks like this:
You need a dashboard to show the outstanding claim amount for the latest date in the data set; 3/1/18.
To accomplish this, we’ll have to use a Level of Detail expression. The first time I tried to solve this problem I created a calculation like this to try and filter to the latest date:
Then I remembered that Tableau doesn’t like when you try to mix aggregate and non-aggregate expressions.
So here’s what I wrote instead:
Notice the addition of the curly braces around MAX([Date])? Curly braces alone mean that you are applying the FIXED Level of Detail expression at the database level. It’s also a workaround so Tableau no longer sees “MAX([Date])” as an aggregated field.
I then used that calculated field as a filter to only display the latest outstanding claim amount:
Want to learn more about how to get the most out of Tableau Calculations? Check out our Mastering Tableau Calculations Workshop.