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 Banoub
before 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.
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
Thanks for doing this!
ReplyDeleteHi, I am trying to use Date Updater Configuration for dashboard to display 1st of every month on from date parameter and current system date in To Date Parameter. But unable to use this extension even after giving the permission to access my data.
ReplyDeleteCan you email me with additional information? Flerlagek@gmail.com
Delete