Date filters are one of the most common controls we put on our Tableau dashboards here at Alight. Normally, we use parameters (not quick filters) to allow our clients to choose the timeframe they want to see: this makes it easy to apply them across all data sources but, until dynamic parameters are implemented in Tableau (I hear this is a popular request for a future release), it requires us to update and republish dashboards whenever we need to change the default date values.
I’ve seen parameter dropdowns with Month and Year from current back through January 2012 and earlier! This is easy to implement but not very user friendly: it takes time to figure out the sort order and scroll to find the correct value. And if the new values are at the bottom (the default when adding values to a parameter in Tableau), the user will likely have to scroll every time they change their selection.
To work around this, we’ve developed formulas to implement an “Auto” function in date parameters. This method works best for Month and Year but should also work for Week and Year. It will not work with calendar pickers. When we build the dashboards, instead of directly using the parameter values in our filter sets, we use calculated fields.
Consider a dashboard with two dropdowns, Year and Month, both showing the value “Auto” by default. Each time the dashboard is viewed, the Year and Month are determined dynamically! I’ve shown how to do it below – expose both parameters to your users, and apply both filter sets to each of your worksheets. Here’s how to do it:
Note: In this example, [date] is assumed to be the date dimension in your data source.
Create Parameters:
- P.Year as String; datafill with the years (“2012”, “2013”, “2014”, etc) and “Auto”; set the current value to Auto
- P.Month as String; datafill with the months (“January”, “February”, etc) and “Auto”; set the current value to Auto
Create Calculated fields (needed for each data source):
- Month#:
case lower([P.Month])
when ‘auto’ then (month(today())+10) % 12 +1
//in our case, we want to view the last full month
//this gets tricky when the current month is January
else month(date([P.Month] + “/1/” + str([P.Year])))
end
- Year#:
case lower([P.Year])
when ‘auto’ then year(today()) – int([Month#]/12)
//we have to decrement the year if it’s January
else year(date(“1/1/” + [P.Year]))
end
Create filter sets (needed for each data source):
- S.Year year([date]) = [Year#]
- S.Month month([date])= [Month#]