Evolytics Blog

How to Change Date Aggregation on X-Axis in Tableau Using Parameters for Time Series and Trend Analysis

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.

time series aggregation Tableau

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?

Date Granularity Tableau

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.

creating string base date parameter Tableau

 

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.”

Granularity Calculation Settings Tableau

 

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:

Time series date aggregation control Tableau

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.

Katie LeasHow to Change Date Aggregation on X-Axis in Tableau Using Parameters for Time Series and Trend Analysis

Related Posts