Update Your Date Filters Automatically

 

Update Your Date Filters Automatically

[In my best infomercial voice] Have you ever created a dashboard with a date filter slider that you had to manually update and republish each month?  Have you ever presented one of these dashboards to leadership just to realize you were showing data that was 6 months old?  Have you ever wanted to make your dashboard update to most recent date automatically?  If you answered YES to any of these questions, then I have just the thing for you.

The truth is, this is a very common occurrence.  We have a dashboard with a date filter (usually a slider) at the top to allow the user to explore different date ranges.  On the backend, the data is updated every month and sometimes every day, yet that date filter slider remains stuck where it was last saved.  If I want to update it to be the current day (or month), then I have to open the workbook, change the filter, then republish it.  If data is updated daily, then I would theoretically have to reopen and republish the dashboard daily to guarantee that it is always up to date.  Truth is, this doesn’t happen (nor is it efficient) and this is why we ultimately end up in a situation where we find ourselves presenting stale data to leadership, our customers, and investors. 

THE PROBLEM

There are many options on how to set filters, but I have not seen one that allows you to set a date slider to update to the current date while allowing you to change both the from and to sides of the slider.  You can set it to anchor to today’s date, which will update each day, but you have no ability to change that date.  I’m not going to get into all the what-ifs, but if you want to hear about all the scenarios and potential options, check out one of the longest threads in Tableau community history which talks about this very thing:  Quick Filters to Always Show Last Date.

But [again in my best infomercial voice] there is a solution…Tableau extensions.  (And, I have a hacky little trick to share if you are unable to utilize extensions).    

EXTENSIONS

If you are not familiar with extensions, they were released in version 2018.2.  They allow you as a Tableau developer to add unique features to your dashboards that are not currently integrated into the Tableau platform.  There are a dozens of them currently available on Tableau Extension Gallery on GitHub.

Before we go on, I would like to warn you that extensions do have some problems.  I’d recommend you read the following blog post from Paul Banoubbefore proceeding:  Tableau Extensions. 

Okay, let’s assume you are comfortable using extensions, especially those in Tableau’s own Extension Gallery and let’s talk about two extensions.  One extension will allow you to automatically update your date range filters and another will allow you to automatically update your date parameters. 

HOW TO USE WITH PARAMETERS

We will start with updating date parameters.  (As a side note, there are a number of reasons to set up date parameters instead of date filters, one of the major reasons is because date filters cannot be applied to multiple data sources and you certainly don’t want the user to have to change three or four date filters in order for it to apply to your entire dashboard).

Okay, so how do we do it?  First, create or find one of your existing dashboards that utilize date parameters.  Let’s assume that my data is updated daily behind the scenes and I want my dashboard to be updated daily as well, i.e. I want it to be filtered to automatically include the most recent data.  I will focus solely on the top/right where I typically place my filters.  You’ll see four filters along with two date parameters (Low Date and High Date).

The first step is to add an extension to the dashboard.  Since extensions are relatively new, I’d recommend first reading the following Tableau Extensions article from Tableau.

Okay, now drag an extension to the dashboard.  When you do so, it will prompt you to go to Extension Gallery or My Extensions.  Click Extension Gallery. 

You will be taken to the Tableau Extension Gallery website I referenced earlier.  On this website, find the Date Updater extension.  It looks like the following:

Click on the Download link, login, then download the DateUpdater.trex.  Save it in your My Tableau Repository \ Extensions folder.  Go back to Tableau and click My Extensions (instead of Extension Gallery).  Navigate to find the DateUpdater.trex file you just saved and open it. 

In Tableau, you will now receive the following prompt (if you don’t receive it, go to the extension window and click the down arrow then select Configure). 

This window will now show all your parameters that are in a date format.  If you have other parameters that are in other formats, they will not show in this window.  From here, you simply select the down arrow associated with your parameter (next to None) to choose how you want to set it up.  You can set it to a variety of options.

I’ve used this dozens of times to ensure my data is as up to date as possible, so I typically use this to update the High Date to “today”.  So just choose the drop-down next to High Date, set it to Today, and click OK.

Now, each time that I open this workbook (or view it on Server/Online), the High Date parameter will be updated to the current day automatically.  No more presenting stale data to your stockholders!  There are other options as well (yesterday, 7 days ago, and 30 days ago), but none are more useful than “today” in my opinion. 

HOW TO USE WITH FILTERS

The process will work much of the same way with a date filter.  In this example, I am using a date range slider.  

Like before, bring an extension onto the dashboard and go to the Extensions Gallery.  Download the Dynamic Date Range Filter extension and install like before.

Now go back to the dashboard and select this extension from your My Extensions area (as you did above).  Click on Configure or select the down arrow then select Configure.

This extension looks a bit different than the date parameter extension, but they are basically set up in the same manner.  Here, you must use a moment.js expression to set your from and to dates.  These are quite simple and examples are shown in the configuration menu.  You can use () for the current date and a variety of other options for other dates.  As an example, ().subtract(24, ‘months’) would set the from date to 2 years back from the current date.  Once you have these set, click Save and Apply.

Now, every time you open the dashboard, your filter will reset to that range, AAANNND if you act now, you you retain the ability to change the slider in any way that you want.  (You don't have to act now...you can change the slider in any way you wish, from date or to date).  

SERVER ADMINISTRATION

In order for extensions to run on Tableau Server or Tableau Online, an administrator must allow access to them.  See the Tableau’s OnlineHelp site for more information.

Also note that when you open a dashboard from your server that utilizes extensions, it will prompt you to “allow” that extension.  It will do this every time.  You may want this to happen each time, but if you do not, please take a look at the following Online Help post that guides you in how to allow specific extensions automatically. 

BUT I CAN’T USE EXTENSIONS

If you are unable to utilize extensions due to the version you are currently using or company security concerns, there are some other workarounds to handling this (none of them are perfect or as easily accomplished as extensions…in my opinion).  If you do a Google search, you’ll find a bunch of workarounds and many of them have been discussed in Tableau Community thread I previously mentioned.  However, I’d like to add another option courtesy of Jeff Shaffer. 

First, go to your dashboard.  Now right-click on the date filter and choose Edit Filter.  Choose Range of Dates at the top and set your maximum date to be some date well into the future.  For my example, I chose 3/6/21 (two years into the future).  So, for the next two years, my date filter will be set to the maximum date in my data – make sense?  (You’ll have to make this same adjustment in two years…set yourself a timed reminder).

Okay, we’re done, right?  Well…not really.  The problem with this is that your date filter will show this date well into the future, i.e. your viewers will see 03/06/21 when it's only 2019.  This could certainly be very confusing for the users of your dashboard. 

So, let’s fix it.  First, right-click your date filter and choose Customize, then uncheck Show Readouts. 

This will remove the date labels from your filter and it will now look similar to the following:

Now create two new worksheets: WindowMin of Date and WindowMax of Date.  Before doing anything with those worksheets, find one of the existing worksheets in which the Date Filter is being applied.  Right-click on it and apply that filter to the WindowMin of Date and WindowMax of Date sheets. 

In the WindowMin of Date worksheet, create a new calculated field called WindowMin of Date (just like the worksheet).  The calculation should read:

WINDOW_MIN(min([Date Filter]))

Drag that to your Text shelf to display the date.  It should display the lowest date that is being filtered with the Date Filter you applied.  (As a side note, this calculation will actually show the minimum date in your data, so your filter may show 6/5/16, but if there is no data for that date, it might show a higher date, such as 6/6/16).  Go ahead and remove any borders and make the background transparent. 

Now, repeat the same steps for WindowMax of Date using WINDOW_MAX(max([Date Filter])).  Drag it onto your text shelf to display the max date.  Again, it should show the max date in your data. 

Go back to your dashboard and find your date filter (the one in which you removed the readouts).  Drag and drop your “WindowMin of Date” worksheet onto the dashboard directly below the left side of your date slider.  Now drop your “WindowMax of Date” worksheet onto the dashboard directly below the right side of your date slider.  Now if you adjust the slider, your min and max dates should also change. 

There is one thing to be aware of if using this method.  The window max date in the example is showing 2/22/19, however, the date filter itself is set two years into the future (3/6/21 to be exact).  If you adjust the high end of the date slider just slightly, the window max date (2/22/19) will stay the same.  That is because the slider is reducing back from the 3/6/21 date.  So if you move it just slightly, it may move it back to 2/2/21, for example, and the window max date of 2/22/19 is still accurate.  So you will need to make more intentional moves on the high end to truly affect the window max date that is being filtered and displayed. 

WRAP-UP

You can do some really flashy things with extensions, but they can be so incredibly useful in our day-to-day work…especially with dates.  I’d recommend that you take some time to learn more about extensions, their capabilities, and security concerns to see if they will work for you and your operation.  They are incredibly powerful!

I hope you found this blog post useful and as always, please reach out to me if you have any questions or comments. 

Kevin Flerlage, March 19, 2019 | Twitter | LinkedIn | Tableau Public