How to Use Tableau Sets to Create Relative Date Filters

An easier solution for filtering to a relative date range

Default date filters in Tableau have (1) limited and static anchoring options, (2) no formatting options, and (3) they require manual range selection. Learn how to overcome all these limitations with an easy and elegant solution to filtering date ranges!

Calculated field that filters the relative date range to the parameter value selected:

CASE [Relative Date Range]
WHEN “Last 7 Days” THEN [Last 7 Days]
WHEN “Last 30 Days” THEN [Last 30 Days]
WHEN “Last 90 Days” THEN [Last 90 Days]
END

Hi, this is Ryan with Playfair Data TV, and in this video, I’m going to show you how to use sets to create easier relative date filters. Before I show you, let’s take a look at why this is needed, because you might be thinking, well, you can already do relative date filters in Tableau, and that’s true. But let’s take a look.

If I drag the date field to the Filters Shelf and choose Relative Date, you can set up a relative date filter. So if you’re not familiar with this, it’s based on an anchor. So I’m recording this video on November 10, 2019. We could say, from today, show me the last seven days, show we last 14 days, or last N days. That’s a relative date filter.

Tableau does come with that functionality, but what you see is what you get here. There’s a few limitations to this. There’s very limited formatting options if we were to show this filter to our end user. You can choose a different anchor, but those anchors are static and require manual selection. And also choosing the relative date range also requires a manual selection if you’re ever wanting to switch it.

I’m going to show you an alternative way to do this, which isn’t actually that hard to set up. It is on the Advanced track because it gets a little bit in the technical weeds, but it’s going to help you create better relative date filters with essentially whatever user experience you would like to create. So I’m going to cancel out of this, and one other note before I show you how to do this. As I said, I’m recording this on November 10, 2019.

The Sample Superstore data set currently runs through the year 2019, so I’ve also added a filter to this data set to pretend that my business updates my data set once per day, and I have data through last night, so through November 9. If you’re trying to follow along and things aren’t quite working, it’s probably because this is truly going to be relative to today’s date. So take note of that if you’re trying to follow along.

The first thing we need to do is create a set out of our element of time. So I’m using Order Date. I’m going to right-click on Order Date, hover over Create, and click Set. And for this illustration, I’ll pretend that I will allow my user to choose from three different relative date ranges– last seven days, last 30 days, or last 90 days. So this first one will be for the last seven days in my data set.

And instead of selecting individual dates here, the important thing is to navigate to the Condition tab, and we’re going to type in a specific formula. This will be a Boolean formula that says the Order Date is greater than or equal to today’s date minus 7. So that’ll get me one week’s worth of data with yesterday being the latest date in my data set. That’s what this formula will do. That is the entire formula, so I’ll click OK, and I’ll have my first set– Last 7 Days.

Now that I have the first set, I can just duplicate that and make a couple of tweaks to it to create my other two ranges. So this one will be my Last 30 Days, and I just need to change the 7 to a 30 and click OK. And then I will duplicate that one and create one last relative date set for Last 90 Days. Click OK.

So change the name. Change the number of days it’s subtracting. Click OK.

All right, before we show you the next step, I would like to do some quality checking. Anytime I set up anything new, essentially, in Tableau, I quality check it before I can trust it, move on, and use it in a view. So I’m going to add my order dates to the Columns Shelf, and so this is all four years worth of data.

As I mentioned, it goes currently through 11/9 because I added a filter to it, and I’m going to drag the Last 7 Days set to the Filters Shelf just to test this out. And sure enough, I’m left with 1, 2, 3, 4, 5, 6, 7 dates, and the latest date is yesterday. So I believe that is working. I’ll assume the others are working because I just duplicated the formula and changed it to 30, changed it to 90, so we’ll call that good.

The next thing I’m going to do, to allow my user to choose which of these date ranges is selected and being used in the view, is parametrize those. So I first need to create a parameter, and I’ll call this the– I’ll call it Relative Date Range, and the data type will be String. That way, I can call these relative date ranges whatever I want.

And it’s a very specific list of only three options, so I’ll choose List for the allowable values and type out those three options. So Last 7 Days, Last 30 Days– again, because the data type is String, you can call these whatever you want. But I’m just trying to make them intuitive, so very descriptive. That’s the entire parameter. I’ll click OK.

I now need a calculated field that gives Tableau instructions for what to do with each of those choices, so I’ll call this c. Relative Date Range. The “c-dot,” that’s just my naming convention to tell me that this is the calculated version and not the parameter. And the formula is CASE Relative Date Range, WHEN Last Seven Days THEN, I want Tableau to show the Last 7 Days set. When it’s the Last 30 Days, then I want Tableau to show the 30 day set, and when it is 90 days, I want Tableau to show the 90 day set.

So that is the entire formula for that. I’ll go ahead and share that in the related content below the video, but this is a tactic I implement all the time to parametrize dimension and measure selection. There’s a couple of videos here at Playfair Data TV to explain that if you need more context on it. That will also be in the related content below the video.

So I’m going to click OK, and I’m going to throw a measure on here. It’s just kind of bugging me to have that empty table there. But this is just a relative date range for the last seven days, but we’ve now parametrized this filter. So I’m going to drag my c. Relative Date Range dimension onto the Filters Shelf and replace Last 7 Days and click True.

And if I did this correctly, we should not see anything change, and that is because, if I show the parameter control for Relative Date Range, the first allowable value was Last 7 Days, so we just replaced Last 7 Days with Last 7 Days, which is why it looks exactly the same. But now, if I click Last 30 Days, this line graph should update automatically to show me the 30 day relative date range. If I choose Last 90 Days, it will update to show me the last 90 days.

So we’ve already made relative date ranges better. These are a little bit more elegant in my opinion. They’re easier to set up. It was very easy to allow the end user these three choices with the dropdown.

You could stop there if you would like to. I’m going to show you one other little hack that allows the user to click a button to change that date range. The reason it’s a hack is, normally when I parametrize things in this way, and combine them with parameter actions, which I’ve explained on another video here at Playfair Data TV, I suggest that you use control sheets. So what you can do is just have an Excel file containing those three options, connect to that data source, create a table, and then setup a dashboard action that says, if you click one of these buttons in this table, it will filter the view. Well, I’m going to show you a little trick to getting around having to setup a second data source.

There’s three options in my Relative Date Range dropdown here. So if I have a dimension containing three dimension members, I can basically change the aliases of those dimension members so that they match the allowable values, use that to create my control sheet, and then use that with parameter actions. I’ve been working with the Sample Superstore data set, as a lot of you have, for a very long time, so I know that Segment contains three dimension numbers. You could confirm that by right-clicking on it and clicking Describe, and there they are– Consumer, Corporate, and Home Office.

So on a new sheet, I’m going to drag the Segment dimension to the Rows Shelf just so I can see what they’re called. But then I’m going to make a calculated field out of this dimension, and I will call this my Date Range Options. And I’m going to say– I’m essentially just going to rename each of the dimension members in the Segment dimension, so if the segment is called Consumer, then that one, I’ll call Last 7 Days, ELSEIF the segment is called Corporate, then we’ll call that one Last 30 Days. And if the segment is called Home Office, then that is my Last 90 Days.

So essentially, just overriding the names of the dimension members. Renaming them. Click OK.

Note that this is a calculated version of that dimension, so it will have no effect on the actual aliases of that dimension. It’s completely– I shouldn’t say completely unrelated, but it will not mess up the original dimension. We’re just creating a dimension from another dimension.

As with everything, I like to quality check this, so I’m going to drag it to the Rows Shelf to make sure it properly named each of my three date ranges, and it did. Now that I’ve quality checked that, and it looks like it’s working, I no longer need the original names. I’ll drag that away, and this is just turning into my control sheet, so I’m going to call that– call it Control Sheet.

I’ll put Date Range Options onto Text, and maybe I’ll flip the orientation, hide the header. I’m going fast on this because this is completely arbitrary and subjective and up to you. I’m just literally creating a control sheet. So maybe I’ll call this, actually, Select Relative Date Range.

But the trick was, and the point of showing you that, is instead of creating my control values in a third or a secondary data source and then connecting to it, I just kind of hacked around that. I created a calculated field instead. That’s essentially my control sheet data set now.

I then created this little table, and if I were to put these both onto a dashboard together, I could use them with parameter actions. So take note, the current allowable value is Last 90 Days. My line graph is being filtered to Last 90 Days.

I’m going to add a parameter action, and we’re going to say that, if you click on the control sheet, we want to overwrite the Relative Date Range parameter with the Date Range Options dimension. That’s the logic there, and there wasn’t much to choose from, so it was intuitive anyway. But you probably would have figured it out because there’s only a couple options because there’s not much on that worksheet.

But that is the entire parameter action, so if I click OK, click OK again, now if I click Last 7 Days in the control sheet, it overwrites the value. So that parameter action– what it’s doing is it’s taking the dimension member that I clicked on, overwriting the value that’s in the parameter. That value in the parameter is feeding the calculated field that feeds up the set that is filtering this view. So if I click Last 7 Days, note it overwrote my parameter control. I actually no longer need a parameter control because it’s being controlled by this control sheet.

If I click Last 30 Days, it shows me the last 30 days. If I click Last 90 Days, it shows me the last 90 days.

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