The Key to Dynamic Parameters & Some Good Use Cases

In the history of the Tableau ideas forum, dynamic parameters (often referred to as Hooperized Parameters, named after original Zen Chuck Hooper) is the most upvoted feature request.  Lucky for all of us, the Tableau dev team has delivered this feature to us in Desktop version 2020.1, which was released late last night!!!!  I recently spent some time testing them out.  I personally got tripped up in one area, so let me take a few minutes to tell you about their purpose, how they work, the key to making them work, then provide you with a few good use cases.

Dynamic parameters are the same as standard parameters, but with two important added advantages.  First, you can allow your parameters to be updated with values from a field from your data source when the workbook opens.  For example, let’s assume you refresh your extract monthly to include the previous month.  If you are using a date parameter, you can allow that list to be updated with all values from your date field, including the new values (i.e. the newest month).  The values are no longer static and no longer need to be updated manually.  The second advantage is that you can set your parameter to be automatically populated with a value of your choosing.  Using the date example from before, when updating your date parameter list with all values, you can set your date parameter to automatically choose the most recent date.  (Note that my examples focus mainly on dates, but dynamic parameters can be utilized with any data type, not just dates). 

Automatic Population

Let’s first talk about automatically populating your parameter using a specific field.  For this example, I created my own data set that shows a value for each month of 2019 – I encourage you to download this simple data source and follow along with this blog post.  If you are unable to access this via Google Drive, please email me at flerlagek@gmail.com and I will send it to you (or simply recreate it, as it is quite simple). 

In this example, we are going to assume that we are using multiple data sources and plan to use a date parameter to easily filter across those data sources. 

To start out, let’s look at what this parameter would look like in previous versions of Tableau.  Go ahead and connect to this data set in Tableau 2019.4 or earlier.  Create a parameter and call it Date Parameter.  Set it to a data type of Date and make it a List parameter.  Click Add from Field and click on Date (from the data source) as shown below.  When you do so, it will populate the parameter with all the dates in the data source, each month for 2019. 

The problem is…that’s it.  The values are hardcoded into the parameter and will not change unless you manually update them (something I do regularly with my work dashboards).  So how do we fix this?  You already know the answer to that question – dynamic parameters. 

Now connect to the data source using Tableau 2020.1 and create a parameter.  Like before, set the data type to Date and Allow Values to list.  Below this option, you will notice some differences from what you are used to seeing.  The first option is “Fixed”.  Here you can feed in values from your data set and it will remain fixed, just like in previous versions.  If you choose Fixed, the parameter will act exactly like they did in the past. 

However, we can change this to be a dynamic parameter by clicking on “When workbook opens”.  When you do so, you are given the same exact options as before, meaning you can feed in values from a field in your data source and it will populate the parameter list.  However, when using this option, your parameter will be automatically updated when your data from that field is updated.  For example, the data source shows each month from 2019; let’s assume I added Jan 2020 to the data set.  If I utilized this option, the Jan 2020 data would be automatically added to my parameter list the next time the workbook is opened.  This is important to note, the parameter update only happens when the workbook is opened in either desktop or where it is published.  Parameter list values do not update with a refresh.  (Screenshots are from the 2020.1 Beta so may differ slightly from the actual version).  

So let’s go ahead and try it.  Add 1/1/2020 to the data source spreadsheet then save it.  Save your Tableau workbook and close it.   Now reopen the workbook.  Look at the parameter and you will see the Jan 2020 value added to the bottom of the list.  (If you do not, make sure you are connected to the correct data source and not some temp version of the data).

Default Value

When you viewed the above screenshot, you may have noticed another option that was not previously available.  That option is “Value when workbook opens”.  You can set your dynamic parameter to default to a specific value when the workbook is opened. Click on this drop-down menu.  The only option you should see is “Current Value”:

Okay, we can set it to the current value, but that doesn’t really do us much good.  In this case, we need to create a calculated field to feed into this and use that calculated field to set the default value of the parameter.  So let’s stick with our example from above.  We have data for each month from 2019 and have added Jan 2020.  At any point in time, we want our parameter to default to the most recent date – in this case, Jan 2020.  If we want to get the latest date, we would want to grab the MAX date from the list of dates, correct?   So let’s create a calculation called “Most Recent Date” with a formula of MAX(Date). 

Now click to edit the parameter.  In the “Value when workbook opens” field, choose “Most Recent Date”.  Wait...only “Current Value” shows. What happened?  Where is “Most Recent Date” field?  Well I did the same exact thing in my first attempt.  Thankfully, I learned from Filippos Lymperopoulos (a Product Manager at Tableau) that this field must be “viz-independent” (this is the "key" that I referenced in the title of this blog post).  MAX(Date) would be dependent on the viz, so we can use a fixed LOD calculation to address this.  So let’s change our Most Recent Date calculation to be a fixed LOD: {FIXED : MAX(Date)}.  As a side note, this can also be expressed as {MAX(Date)}.  

I'd like to add a few notes here.  First, although the field used for the "Value when workbook opens" has to be viz-independent, it does NOT necessarily have to be a fixed LOD.  You could create a calculation of TODAY() and since it is viz-independent, it will work as well.  Second, if using a Fixed LOD, it cannot be a nested LOD.  For example, {FIXED : MAX([Order Date]-52*7)} works where  DATE(DATEADD('week', -52 {FIXED: MAX([Order Date])})) does not work.  Basically, you must start your calculation with the fixed LOD, it cannot appear inside of another calculation.  

Okay, with the sidebars out of the way, go ahead and edit the parameter again and choose the drop-down next to “Value when workbook opens”.  You’re Most Recent Date calculation should show.  Go ahead and choose that value.  This sets your parameter to use this calculation each time the workbook opens, which means it will choose the latest date from your Date field automatically upon opening.  How freaking awesome is this????!!!!  Go ahead and try it; set your parameter to a past value, save the workbook, close and reopen.  It will automatically set that parameter to the latest date.  You can then use that parameter with a calculation to filter your view. 

As I mentioned before, this is not applicable to just dates.  You can do it with any parameter data type. 

A Couple of Good Use Cases

Date Range:  I believe that dates will be the most common way in which dynamic parameters are utilized.  The information above showed how to do it with just a single date, but what if you want to do this with a date range?  Well, we can utilize the same methodology above with a few extra parameters and calculations to do exactly that. 

In order to do this, we are going to use the same sample data set and Date Parameter we previously set up (we are basically going to piggyback off the work we already did).  Let’s set up two more parameters: Time Frame and Time Frame Value.  The Time Frame parameter will be a string containing a list of values: day, month, year (lower case).  These can be fixed as we do not need them to be updated based on our data – they can remain static.  The Time Frame Value will be a list of integers from 1 to 31 and can also be a static parameter.  Show those parameters on the screen. 

The plan is to create a date difference calculation based on these three parameters then create a date filter to only show the corresponding time frames.  For example, our data is showing months, so let’s set our Time Frame Value to 6 and Time Frame to month.  I set the Date Parameter to any value for now (12/1/19).  The idea is that when we open the workbook, it will set the Date Parameter to the most recent date and show a total of 6 months (the most recent month and the 5 months prior). 

Create a calculation called “Date Filter Time Frame”:

                  

IF DATEDIFF([Time Frame], [Date], [Date Parameter]) >= 0

   AND DATEDIFF([Time Frame], [Date], [Date Parameter]) < [Time Frame Value]

   THEN 'SHOW'

   ELSE 'HIDE'

END

This calculation uses the time frame to calculate the difference between the date in our data and the date parameter.  It will ensure that the date difference is at least 0 and that it is less than the selected Time Frame Value. 

Add “Date Filter Time Frame” to the filters shelf and check only “SHOW”; do not check “HIDE”.  Add Date to the rows and set it to Exact Date and discrete.  You should only see a subset of your values.  If you used the same values as I showed above, you should see 7/1/2019 – 12/1/2019. 

Save the workbook and reopen it.  The Date Parameter should update to the most recent Jan 2020 date (that we added above) and your view should filter to the previous 5 months: 8/1/2019 – 1/1/2020.  Since you provided the Time Frame and Time Frame Value parameters, you can then allow your user to adjust the date to anything they like (and you can easily change the default settings). 

Dynamically Pull Different Tables from Your Database:  This comes from an actual use case at work.  Each day, we create a snapshot table of our current inventory.  We retain snapshot tables every day for 90 days and prior to that is stored as a monthly snapshot table only.  So on February 16, we would have tables that would look like:

20200215

20200214

20200213

20200212

.

.

.

20191001

20190901

20190801

Etc.

One of our most widely utilized dashboards looks at the information supplied in these tables.  We typically review the most current data, but often have a need to look back at previous dates in time.  In this case, we couldn’t just filter the data; we needed to pull data from a completely different table.  We have hundreds of tables, so combining the tables simply wasn’t reasonable.

The goal with this dashboard was to automatically connect to the most recent table while allowing users to pull data from any date (any table).  We were able to implement a combination of custom SQL and several extensions to make this dashboard work…but it’s not optimal in any way. Dynamic parameters will make this process very simple and in fact, I’ve already mocked it up in the 2020.1 pre-release.

I have a table in my database that lists all of the snapshot table names (like the examples above).  So the first step was easy; I connected to this table via Custom SQL to pull just this Table Name field into a Tableau workbook.  Next, I created a Table Name Parameter.  It was set with an integer data type and list, which was populated with the Table Name field (using “When workbook opens”).  I then created a Fixed LOD to pull the most recent table name, { FIXED : MAX([Table Name])}, and set that as my “Value when workbook opens”. 

Okay, now remember that the above are just values from a field in a table, they don’t contain any actual data.  The ultimate goal is to pull actual data from the actual tables with those names, have it default to the most recent table, but allow users to choose any table.  As an example, let’s saying I’m pulling from the database called Inventory.  I want the dashboard to initially pull all data from inventory.dbo.20200215.

To do this, I created a new data source that utilized another Custom SQL statement.  To the Custom SQL window, I added “SELECT * FROM inventory.dbo.” then inserted the Table Name Parameter as shown below:

When this custom SQL loads, it pulls the table corresponding to the parameter.  And since we set up the parameter as a dynamic parameter with a default value of MAX(Table Name), that means when the workbook is opened, it pulls data from the most recent table AUTOMATICALLY!!!  In addition, the user can use the parameter to select any table in history.  Most importantly, any time a user opens the dashboard, it will check to see if there are different values and update the parameter with those new values.  Incredible!

Wrap Up

Dynamic Parameters are the most requested feature in the history of the ideas forums…and there is a reason for that.  They are incredibly powerful and will automate so much of the manual work that analysts have to do on a recurring basis.  And the use cases described above only scratch the surface of what is possible.  I must say thank you to Tableau and the Tableau dev team for continually providing us with these powerful features!!!!!!!

One last note, I mentioned Filippos earlier in this post.  He wrote a very good overview of dynamic parameters on Tableau’s website and provided a nice use case related to user-based settings.  I recommend you check it out. Thanks for reading and as always, feel free to contact me at any time if you ever have comments or questions.  Enjoy 2020.1!

Kevin Flerlage, February 25, 2020

Twitter | LinkedIn | Tableau Public