Complex Relative Date Filters in Tableau

Setting a last 3 months relative date filter

When you bring a date field into the filters marks card, you have the option to use a relative date filter. This allows you to set a filter that dynamically changes the view as time passes. For example, in the gif above we have set the filter to only show the last 3 months of orders/bookings. The options available are fairly vast and self-explanatory.

However, there are some limitations to this feature. There are some business scenarios where you would want to use a more complicated filter logic. For example, businesses that rely on bookings (without full payment upfront) may be interested in seeing their forecasted revenue for the next 3 months AND their forecasted revenue for the previous month. Although this is quite a simple thing, there isn't a way to allow for both the next 3 months and the previous month using the relative date filter feature.

So how would we solve this complicated filter requirement?

Tableau has many date functions available for use in its calculated fields. We can put these to use to give us the filtering we need.

The function DATEDIFF allows you to specify a unit of measurement (e.g. months) and calculates the difference between two dates in this specified units. The function TODAY dynamically outputs todays date. Using the logical operators AND and OR, we can combine the statements to give us what we need. The output of the calculation is a Boolean, so we get a true or false for each row of data depending on if the [Forecast Date] field satisfies the calculation. By placing the calculation on to the filters marks card and checking true, we get the desired result:

Complicated filter view
Author:
Alban Halili
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2024 The Information Lab