How to Dynamically Change Date Parts in Tableau

Automatically Switch Between Days / Weeks / Months

At times, the number of marks on a view should dictate which date part (i.e. date aggregation) is used. Ryan shares how to automatically have Tableau choose the optimal date part based on the number of days in a range.

Calculated field that computes the number of days in the range

[End Date] – [Start Date] +1

Calculated field that automatically truncates dates

IF [Days in Range] <= 30 THEN [Order Date]
ELSEIF [Days in Range] <= 90 THEN DATETRUNC(‘week’,[Order Date])
ELSE DATETRUNC(‘month’,[Order Date])
END

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you how to dynamically change a date part from day to week to month depending on the number of dates you’re trying to visualize on a view. Allowing the end user to change a date part is a helpful technique because it helps accommodate the number of data points on a view. For example, if I’m wanting to look at a smaller date range, say one week, it’s likely that I’d want the date part or the aggregation of a date to be something like day so I can see each of those individual dates within that smaller date range.

However, if I open up that date range to be something larger, I might want to change that date part to something a little bit– at a higher aggregation like week or month. Because this is such a helpful technique, I’ve shown you how to do this in another video using parameters but this video is going to be different because I’m going to show you how to automate this process. Tableau, the calculations that I’m going to share, is going to look at the number of days in the range and automatically help accommodate for the dates being selected to give you the most appropriate date part.

This technique requires five steps, so it’s a little bit more involved you might think. But if you follow my process for normalizing dates using a parameterized date range so that they are normalized onto the same axis, you will have already created most of these calculations and the first few steps are very short. The first thing we need to do is to create a parameter for the start date.

So over here in Tableau Desktop using the Sample – Superstore dataset, I will choose Create Parameter and call this first one Start Date. That data type should be a Date. And I’ll go ahead and leave the allowable values for the purposes of this example. However, I do know that the Sample – Superstore dataset only has four years of data. So you might want to update that to a specific list or a range of dates, but I’ll leave it as All for this example.

There’s step 1, told you that would be relatively short and easy. Now that we’ve got the first parameter, it’s actually even easier for step 2 because we can just right-click on that first parameter and choose to duplicate it, and then we just need to make one edit to this which is the end date. We need to change the name of that parameter from Start Date to End Date. There’s steps one and two.

I’ll go ahead and show these parameter controls so that we can see what has been selected. As you can see, those parameters are defaulting to the current date. Again, knowing the parameters surrounding– not to use the word and the definition– but the date range in the Sample – dataset, I happen to know only runs through 2018. So, let’s make a little bit more realistic date range, and we’ll maybe start with September 1st, 2018 to September 7th, 2018.

September– so first seven days in the month of September in the year 2018, which is the latest year in the Sample – Superstore dataset. Step three is to create a calculated field that computes the number of days in that range. I always call this Days in Range. And there’s a couple of ways to write this formula, but because those parameters both go down to the day level, the most elegant way to write this formula is simply ‘End Date’ minus ‘Start Date’ plus 1.

So, this will take the date in the End Date parameter minus the date selected in the Start Date parameter and add one day to it. The reason we are doing that one day is to capture the current day. For example, if I chose 9/1 to 9/1, September 1st minus September 1st is a range of 0. So that plus 1 just helps capture the current day. That’s step number three. Step number four is to create a date filter because we are no longer going to be filtering the marks on the view by the traditional Order Date field that comes with the sample dataset.

We’re actually going to be filtering the dates out based on the parameter selection. So, we need to make a calculated field for that, it’s a fairly simple one. It’s just going to be the date that you’re using in your dataset must be greater than or equal to the Start Date. And the Order Date from your dataset must be less than or equal to the End Date. So there is our date filter. That is step number four.

And step number five, this is what really makes this technique go. I’m going to call this Automated Date Part. And you can set this up with whatever criteria you would like. But again, the spirit of what we’re trying to do is to dynamically change the aggregation of the dates, the proper term for that is a date part, based on the number of days that are selected in this range. When there’s a smaller range selected, I want to show a more granular date part. When there’s a larger range selected, I want to show a less granular date part selected or being used in this visual.

For the purposes of this example, again, this can be anything. This is completely arbitrary but just for the purposes of illustration, let’s say that if the date range contains fewer than 30 days, we want to look at a day date part. If it’s between 31 and 90 days, we will change that date part to week. And if our date range is greater than 90 days, we will change it to be a date part of month. So the calculation will be IF Days in Range, which we created in step number 3, is less than or equal to 30, then I just want to use the date field from my dataset.

ELSEIF, the second piece of criterion here, is if the number of days– if it makes it past that first line, so if it’s greater than 30 but it’s less than or equal to 90, then I want to take the date but truncate it at the weekly level. This is the formula for that. And I’ve mentioned this on a few other videos, but any time you see a blue function in this calculated field dialog box and you have never seen that before, if you click on it, it will open that up in this little data dictionary, give you a definition of what it’s going to do, as well as the syntax of how it is used.

But essentially the second line so far is saying if the number of days selected in these two parameters is less than or equal to 90, take the date truncated at the weekly level. So in other words, we’re just converting the date part from day to week. And then everything ELSE, so if you’re looking at this logic, this would mean any date range greater than 90, we’ll do this DATETRUNC function again. But this time, we’re going to do it at the monthly level.

And this IF / THEN logic always concludes with an END. Then, of course, I’ll share this calculated field in the related content below the video so you can just copy and paste it over. Again, if you want to change the ranges, simply change these numbers, the 30 and the 90. Those can be whatever you would like. But I’m going to go ahead and click OK. There’s my Automated Date Part. And to illustrate that this is working, I’m going to make a quick line graph that looks at the Sales measure by this Automated Date Part.

If you’re not familiar with the line graph, this is on the Fundamentals learning path. But this is a shortcut that I like to use to create my line graphs. I right-click on my element of time while I drag it to the Columns Shelf, and that allows me to choose whether that date will be used as continuous or discrete, as well as choose the date part. Remember, this date part is now being automated so I’m just going to choose this very first option, which will just use this date as a continuous field and then the dates in these parameter controls will tell it whether that date part should be day, week, or month.

So, I want to choose Automated Date Part Continuous. Notice we’re seeing by default the entire four-year range of data in the sample dataset. This is where that Date Filter from step 4 comes in. This was a Boolean formula, that’s what that t and f icon means. And I’m going to put this on the Filter Shelf to only contain the dates selected in these two parameter controls, that’s what this is doing. If I click OK, we should just see the seven dates that match that selection.

Because this date range is less than 30 days, we are seeing a date part of day on the view. But if I bumped this up to, say, look at two months of data. So I’ll go September 1st through October 31st, now notice what happens. Each of the date parts is now a week. It goes from September 2nd to September 9th, September 16, and so on. If I get to a range larger than 90 days, so let’s say that I want to look at the last four months of the year, the date part changes once again automatically for us because it meets our logic that said if the End Date minus the Start Date is greater than 90 days, I want to see a month date part.

So that’s working for us. Just to show you the last example, if I were to flip back and just look at maybe Christmas week, which would be a shorter date range than 30 days. So, December 25th through December 31st, it once again flips back. Take a look at the dates on the axis. We’re now looking at days.

This has been Ryan with Playfair Data TV – thanks for watching!