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









13 comments:

  1. Hi, 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.

    ReplyDelete
    Replies
    1. Can you email me with additional information? Flerlagek@gmail.com

      Delete
  2. Thank you for the info, your posts always have something to learn.

    Quick question, I'm trying to make dashboard which show user different result base on their login time(e.g User will see last 4 hours orders if they open dashboard during business hours,but they will see all orders for the day if they open same dashboard after business hours). I build that dashboard by subtracting TODAY() from NOW() and figuring out the difference btw time and then right formula based on it.which is working but i would like to know if there is any easier way to do it?

    Thanks,
    POUYA~MH

    ReplyDelete
    Replies
    1. Well, Today is a date and Now is a datetime...so I don't think that will work. I'd be happy to mock this up for you, but I'd like to provide you with a sample workbook. Could you email me at flerlagek@gmail.com?

      Delete
    2. But if this helps...I would create this and place it on the filters shelf and set it to SHOW values only.

      // Show last 4 hours if during work hours
      // Show all orders for the day if not during work hours
      // Assume work hours are 8:00 AM to 5:00 PM


      // Evaluates if it is within working hours
      IF DATEPART('hour', NOW()) >= 8 AND DATEPART('hour', NOW()) < 17 THEN

      // When viewing during work hours then...
      // Returns Show or Hide if the orders were completed in the past 4 hours.
      (IF DATEDIFF('minute', [Date Time], NOW()) >=0 AND DATEDIFF('minute', [Date Time], NOW()) <= 240 // last
      THEN 'SHOW'
      ELSE 'HIDE'
      END)

      // When NOT viewing during work hours then...
      // Returns Show or Hide if the orders were completed during the current day
      ELSEIF DATEDIFF('day', [Date Time], NOW()) = 0
      THEN 'SHOW'
      ELSE 'HIDE'

      END

      Delete
  3. thank you - i will email you

    ReplyDelete
  4. Hi Kevin, thanks for post, it helped me to get through my situation but I am halfway through. My situation is, I have a dashboard which gets updated with day-1 data every day. The dashboard has date range filter which needs to be update with latest date whenever we have a new date. I have downloaded and implement the extension file as instructed in your post. I am using the formula ().subtract(60,'days')(August 29 2020) for lower bound date and ().subtract(1,'days')(October 27 2020) for upper bound date when I implemented on October 28 2020. So far everything works fine with lower end date as 8/29/2020 and upper end as 10/27/2020. But surprisingly after 5pm PST the dates move forward as 08/30/2020 and 10/28/2020. My wild guess is this might be due to the date change in UK timings. Can you please help me with this. Thanks in advance.

    ReplyDelete
    Replies
    1. Wow. That is super interesting and yeah, I'm sure that is the reason for it. How about using two dynamic parameters to set those dates? You could set the default dates to Today -1 and Today - 60. This blog post might help: https://www.flerlagetwins.com/2020/02/the-key-to-dynamic-parameters-some-good.html

      Delete
    2. Hi Kevin, thanks for the reply. I will try out the parameter functionality as you are suggesting, but my requirement is having the same functionality as single slider like the filter. Will the parameter have the same functionality?

      Delete
    3. VJ, sorry for the delayed response...I must have missed this. No, unfortunately it will not. It would be set with two parameters, a low date and high date. This is a challenge that so many have had. In the above blog post, their is a link to a long community forums thread where all of this is discussed in great detail. But ultimately, I think this will be a challenge for you.

      Delete
    4. Lol. I was about to comment that fixed max date can be used to set parameters to be dynamic. I also use {fixed: max(date)-365} to set 1 year ago date. Pretty handy for keeping date ranges to 1 year. max date inn the table is usually not today. So, date range filter excludes some days.

      Delete
  5. Thanks a lot for this!

    ReplyDelete

Powered by Blogger.