Ads Top

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)}.  (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.)

Now 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










24 comments:

  1. Hi, That was a great article. Thanks for sharing.
    I have a scenario where I need to show the months (like Mar 2020, Feb 2020..) based on the selected year(parameter).
    When I map the date field to fetch the months, then they are duplicating based on the dates.
    Also I would like to show the months in descending order based on the month number (i.e) latest month on top. How shall we achieve this?

    ReplyDelete
    Replies
    1. Thank you for the kind words. I'd be happy to take a look at your workbook. Could you send me your TWBX or a sample TWBX for me to review? You can email it to flerlagek@gmail.com.

      Delete
  2. Thank you, This helps for me. It seems the default value works fine on tableau desktop, but for some reason to the online version, the default value will not change immediately when you data source refreshed. it works better in Edge than Chrome.

    ReplyDelete
    Replies
    1. It will only update when the workbook is opened. It's not necessarily about the data source being refreshed, it's only when the workbook is opened.

      Delete
  3. Thanks for the great article, Kevin! The "key" of viz-independent default calculations is what I had been missing when trying to unlock the power of dynamic parameters. Your article taught me the way!

    ReplyDelete
    Replies
    1. Oh that's fantastic, Jake! Glad I could help.

      Delete
  4. Hello, thank you for the post and tutorial. Everything worked until I tried the last part on "Dynamically Pull Different Tables from Your Database". I followed your steps exactly and with your data.
    When I closed and re-opened the workbook, the parameter did not have any possible value (same when publishing the workbook online). When I click to edit the parameter, a message at the bottom says: "Two errors occured when configuring the parameter".
    I can't see any detail on these errors.

    Can anyone else see this?

    ReplyDelete
    Replies
    1. Would you mind sending the workbook (as a TWBX) to me so that I can check it out? Flerlagek@gmail.com. - Thanks!

      Delete
    2. Hi Kevin, I just sent you an email with the file. Thanks for looking :)

      Delete
  5. Great article: I have a scenario where I need to use parameters on product categories (rather than filters) but I also need an All (like a filter) so users can see across categories as well. We have a formula that can do this in the current parameter, but want to take advantage of a dynamic build feature as well. It's asking a lot, I know. Thanks!

    ReplyDelete
  6. Well, that is a challenge and one that I have encountered as well. There are a couple options that I know of, none being perfectly optimal. First, you could create a fake row and union it to your dataset. That would include your ALL value. But better yet, since parameters work across datasets, you could create a new data set that does something similar but only with that one field. So just use a select statement in Custom SQL to pull your data, union it to a value of ALL and then use that for your dynamic parameter. The other option would be to create a new parameter. That parameter would be called something like "Show All or Select". It would have two values: All or Select Individual (or something like that). Users would use this to choose whether they wanted to show all values or to select from the other parameter (the one you set up to work dynamically). You'd have to modify all your calcs to take that one into effect. Let me know if any of these options work for you.

    ReplyDelete
  7. Hi Kelvin,
    Do I have to use this parameter in a calculated field for its to default to whatever I want in view after opening.

    ReplyDelete
    Replies
    1. You must use a "viz independent" calculation. What are you hoping to be able to do?

      Delete
  8. Hi Kelvin, thanks for sharing! I have a problem,
    My calculation: Makedate(Year({Fix:Max(date)}),1,1), but I received a warning of tableau: "... value couldn't be assigned to this parameter". Is my calculation a "viz independent" calculation???

    ReplyDelete
    Replies
    1. To be honest, I'm not quite sure why that doesn't work, but if you wrap it with Fixed it will work: { FIXED : MAX( Makedate(Year({FIXED :Max([Order Date])}),1,1) )}

      Delete
  9. Hi Kevin ! How do you think the dynamic parameter would work under wbk subscriptions ? Manually, this works flawlessly, but do you think that a subscription would trigger the exact same behavior as "..when the workbook opens..." ? What's the idea..to receive three times a day an email containing the dashboard, refreshed with new data (after an scheduled full refresh runs the subscription), but always filtered to the last date (dynamic parameter).

    ReplyDelete
  10. Thanks for the tip. Worked perfectly!

    ReplyDelete
  11. Hey Kevin, I'm a bit confused about implementing this. I have a parameter that is being fed when the workbook opens, and is a list of string values. I'd like to create one additional value for "All" that can be selected in the drop down, and used in a filter.

    EX. IF Param = 'ALL' Then TRUE, etc....

    I created a calculated field with the value 'All' and can use this as a default, but I get a warning that this value is not in the parameters list of values, and it does not show within the drop down.

    Any ideas on how I can get around this?

    ReplyDelete
    Replies
    1. Yes, unfortunately that will not work. You can only set the default value to something that is in your list of values. You could add a row to your data that includes a value of ALL (you could union a 1 row table). The other option when I want an "ALL" option is to utilize sets instead and the new Set Control options may be perfect. But that really depends on your use case. If you have more detail and can share, feel free to reach out to me at flerlagek@gmail.com.

      Delete
    2. Thanks Kevin! I went with the Union method and it worked perfectly!

      Delete
  12. Hi, I am using two data sources and both are in sql. I have made a column in one data source which has rows for dates in which I will add one row each month. Now the dashboard should update automatically with the latest month showing in parameter when the workbook opens. Now, the problem is that when I open the workbook, the parameter control box is shown empty initially but when I add a new sheet in the dashboard and go to the same dashboard again, the parameter values show up in the parameter control box. Any idea why it is behaving like that?

    ReplyDelete
    Replies
    1. Any chance you could share that workbook with me, flerlagek@gmail.com?

      Delete

Powered by Blogger.