The world’s leading publication for data science, AI, and ML professionals.

Date drill-down/drill-up in Tableau

When we are working with date fields in Tableau, there is often a need to drill down into it and get a much higher level of granularity…

Understanding how to work effectively with date hierarchies

Photo by Henry & Co. on Unsplash
Photo by Henry & Co. on Unsplash

When we are working with date fields in Tableau, there is often a need to drill down into it and get a much higher level of granularity. For example, while looking at the data year-wise, we may see some trend and want to further explore it and look at monthly trends or even quaterly trends. Tableau has great hierarchy options, however, when you are publishing a dashboard, aesthetics matter.

Image by Author
Image by Author

Having the user click on multiple buttons to drill down complicates the usage further. Also, only familiar tableau users might think of exploring the dashboard using the technique shown above. New users would not be aware of this functionality.

In this post, I will show an easy method to switch between different date levels using basic calculations and parameter fields in Tableau.


Data

For illustration purposes, I have used the in-built Sample Superstore dataset in Tableau, namely the Orders sheet. The version of the data used is 2020.3


Creating functionality to drill down/up on Dates

Step 1: Create a new parameter called ‘Date format’

Create a list value parameter called Date format to store the different date types you would need to drill-down into your data as follows:

Image by Author
Image by Author

If you want the default dates to be in Month format, you can set the Current Value as Month instead of Day.

Once the parameter is created, show parameter control in order to let the user switch between the level of date detail required.

Step 2: Create a calculated field ‘Date calculated’ to change the date formats

Now that you have the parameter created in order to store the different date formats, you need to link any change in the Date Format parameter to the dates being displayed in the dashboard. Create a calculated field that would automatically change the format of the dates as the parameter value changes.

Image by Author
Image by Author

Here, by default, the if clause kicks-in and shows the values as dates since I set the Current Value in the Date Format parameter as ‘Day’. If I had set it as ‘Month’, the IF clause would have appeared as follows:

IF [Date Format] = 'month'
THEN STR(MONTH([Order Date]))

For ‘Month’ as Current Value in the Date Format parameter, the ELSE clause would remain the same as shown in the image above.

Step 3: Build the worksheet

Once the calculated field is created, all that is left to do is create the sheet and let the magic unfold!

Drag Sales to Columns and Order date to Rows which would show the dates as Years. Additionally, drag the calculated field of Date calculated to Rows, placing it next to Order Date. Now that your worksheet is ready, you can change the date format values using the parameter control and voila you can drill-down/drill-up the sales data based on the date values.

Image by Author
Image by Author

ISO-8601 v/s Standard Gregorian Calendar for dates

Often, it so happens that the weeks in a year start from Week 2 and end at Week 53. In the above example itself, there was a discrepancy in the numbering of the weeks. 2018 had weeks starting from week 2 and ending at week 53 and this was seen in 2019 as well.

Image by Author
Image by Author

On further exploration and looking at data from week 53 of year 2018, we see that it has a mix of order dates from January and December.

Image by Author
Image by Author

This happens because we are using the Standard Gregorian calendar. In Greogorian calendar, weeks can extend across years. The ISO-8601 standard however, has a consistent number of days in each week and a consistent number of weeks each year. Calculating weeks in ISO format would resolve this issue.

To do so, simply go to the Date Format parameter created and change week to iso-week as follows:

Image by Author
Image by Author

Doing this, fixes the issues seen with respect to the number of weeks and now, every year has weeks starting with 1 and ending at 52.

Image by Author
Image by Author

We can now effectively perform date drilling either to get a more detailed look date-wise or a concise summary.


For anyone interested in checking out this worksheet/dashboard, the files can be found on Tableau Public. I can be reached on LinkedIn or Twitter.

Thanks for reading and happy drilling! 🙂


Related Articles