This post is part of our Six Favorite Tableau Tips, Tricks and Hacks to Enhance Dashboards collection.

The Problem

Tableau makes selecting and changing date dimension aggregations 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?

The 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])
END

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!

Final Workbook on Tableau Public