Tableau Tip: How to Make Better Relative Date Filters

To create this set of dates, right-click on your date field on the Dimensions area of the Data pane, hover over “Create”, click “Set…”, and navigate to the Condition tab. Here’s how the settings look for this first range using the Order Date field in the Sample – Superstore dataset (which I updated to have 2019 dates).

I’ll quality check that the correct date range is being computed by creating a sheet with continuous Day of Order Date on the Columns Shelf and the newly created Last 7 Days set on the Filters Shelf. Sets are Boolean, so each Order Date is either in the set or not, and when a set is placed on the Filters Shelf, the default behavior is for Tableau to only display the dimension members in the set.

So far so good – we’ve got seven days in the range, and they’re the last seven days we’ve got data.

This type of relative date range we created using the Condition tab of a set can be used for any range of dates. To finish the illustration, I’ll create two additional relative date sets; one for the last 30 days and one for the last 90 days.

Relative Date Set Keeping the Last 30 Days

Relative Date Set Keeping the Last 90 Days

How to allow users to select the relative date range

Now that we have our relative date range options set up, we can allow users to decide which set is filtering the dates on the view. This works the same way as the classic Tableau tactic of allowing users to choose which dimensions and measures are being used in a view.

First, create a parameter with a data type of String and your list of options.

Next, create a calculated field using CASE / WHEN logic to tell Tableau which set to use when each value within the parameter is selected. Note that the outcomes on each line are the sets created during the previous section of this post.

Let’s try this newly created filter out on this Sales by Day line graph built with Sample – Superstore data.

The three and a half years of daily data are making this view very challenging to analyze. If I place the Relative Date Filter calculated field on the Filters Shelf and choose “True”, we will see only the last seven days kept on the view. This is because the current value of the parameter that is feeding the calculated field is “Last 7 Days”, and when that is the selection, we are filtering on the Last 7 Days set.

If I show the parameter control for the Relative Date Range parameter – which you can do by right-clicking on the parameter and selecting “Show Parameter Control” – the user can choose Last 7 Days, Last 30 Days, or Last 90 Days. Here’s how the view looks after updating it to Last 30 Days.

Improve the UX with parameter actions

Since we are using a parameter to filter on our relative date sets, we can use parameter actions to allow our user to change the relative date range with the click of a button!

First, set up an Excel file with your list of choices. The important thing about these types of data sources that are used to create control sheets is that the Value field must be spelled the same as the values in the parameter. The Display field can be whatever text you want displayed on your navigation.

Next, connect to this Excel data source and create a text sheet that will act as our navigation. You can format this however you would like, but the Value field must be somewhere on the view for this to work during the last step.

Place both the control sheet and the chart(s) containing your relative date filter on a dashboard.

Lastly, create a parameter dashboard action by navigating to Dashboard > Actions… in the top navigation. You will need to add a “Change Parameter…” dashboard action. Here’s how my settings look if I want the selection on the control sheet to populate the allowable value of the Relative Date Range parameter that is filtering the line graph.

After accepting these settings, just clicking on one of the three relative date range options will filter the line graph! Here’s how it looks after clicking on the 90D button.

Notice that the parameter action overwrote the allowable value in the parameter control. We no longer need the parameter control at all because the selection is now being controlled by the text sheet.

How to Use Control Sheets for Better Parameter Actions

Other applications

These relative date filters can also be set up for comparison periods. For example, if you wanted to create a set of dates for the seven days immediately preceding our Last 7 Days set, the condition would be:

If you were to create a calculated field tied to the Relative Date Range parameter like we did in the Relative Date Filter calculated field above, the current value of the parameter would instantly filter both your current relative date ranges and their respective comparison ranges. These comparisons can be used to create one of my favorite dashboard elements, the current versus comparison index callout.

One more thought on how these improved relative date filters can be used. The code shared throughout this post uses today’s date as the anchor. The TODAY() function will dynamically update the values in the set based on the current date. However, you can also use the DATE function to hardcode or even parameterize the anchor.

Let’s say you wanted to analyze the performance leading up to Christmas Day. The condition for the relative seven days set would be:

You could also replace DATE(“12/25/2019”) in this example with a date parameter that allows the user to choose any anchor they want!

Thanks for reading,
– Ryan