Problem: Tableau makes selecting and changing the aggregation of a date dimension very easy when building a worksheet view. However, unless an end user views an individual sheet in Tableau Desktop, they can’t easily change the date granularity to day, week, month, quarter, or year on their own in a dashboard.
For example, it makes sense to switch the granularity of a line graph over time for different types of analysis. Consider the Sample Superstore data packaged with Tableau, which contains four years of daily data.
If you set the date aggregation to year, you see a 10,000 foot view of your sales trend, but no seasonal insight. On the other hand, you can set the date granularity to continuous day to catch outliers, but it’s nearly impossible to differentiate between individual daily data points because you’re looking at 1,427 marks at the same time.
As you can see, viewing your sales over time at different levels of date granularity tells very different stories. Why permanently set the date aggregation of your viz when you can allow empower users to choose for themselves?
Solution: To provide flexibility to change data granularity in a viz, we can create a parameter that includes each date granularity option (i.e. Day, Week, etc.). We also must create a calculated field that acts as the adjustable date aggregation value.
How to Change Date Aggregation Using Parameters
Step 1: Create a Parameter with All Date Aggregations
Create a string-based parameter with each level of date aggregation.
Step 2: Create a Calculated Field to Change Date Aggregations
Create a calculated field, leveraging the DATETRUNC function to change the date aggregation to the appropriate level based on which parameter option is selected.
Here’s the logic for the calculated field:
CASE [Parameters].[Date Granularity]
WHEN “Day” THEN [Order Date]
WHEN “Week” THEN DATETRUNC(‘week’,[Order Date])
WHEN “Month” THEN DATETRUNC(‘month’,[Order Date])
WHEN “Quarter” THEN DATETRUNC(‘quarter’,[Order Date])
WHEN “Year” THEN DATETRUNC(‘year’,[Order Date])
Step 3: Use the New Date Aggregation Calculation in the View
Instead of Order Date, in Columns. Choose “Continuous” and “Exact Date.”
Step 4: Make the Date Aggregation Parameter Available to Users
Ensure you right-click on the Date Granularity parameter and choose “Show Parameter Control” so your end users can change the level of date aggregation.
Your final product will look like this:
Hopefully custom date aggregation changers will be a new powerful tool in your Tableau toolbox! If you want to learn even more Tableau tips and tricks, check our our blog.