Five Awesome New Features of Tableau Version 2020.3

Tableau Version 2020.3 was just released and it is packed with awesome new features.  In this blog post, I am going to introduce you to four of them:  the IN Operator, Relevant Values for Set Controls, Export to Crosstab Button, Write to Database in Tableau Prep, and my absolute favorite, Clearing Parameter Actions. 

 

1. The IN Operator

 

If you are a SQL user, you are probably very familiar with the IN operator and you probably use it all the time.  I’ve heard many questions in the past several years as to why Tableau doesn’t have an IN operator.  Well, they do now!

 

In SQL, the IN operator is most commonly used in a WHERE clause to trim you’re your data set to certain values.  (It is essentially a shorthand replacement for multiple OR clauses).  For example, below the IN operator is used within a WHERE clause to trim the data set down to just records in Kentucky, Indiana and Ohio. 

 

SELECT * FROM TableName

WHERE State IN(‘Kentucky’, ‘Indiana’, ‘Ohio’)

 

IN is very powerful within SQL and just as powerful within Tableau.  Let’s take a look at a simple use case for it.  Let’s assume I am looking at United States sales figures and I am interested in how my local tri-state area compares to the rest of the US.  I may utilize the IN operator within an IF statement to break states into these two categories:

 

IF [State] IN('Kentucky', 'Indiana', 'Ohio')

THEN 'Within Tri-State'

ELSE 'Outside of Tri-State'

END

 

In this case, when the State is Kentucky, Indiana, or Ohio, it will be marked as Within Tri-State with all other states being marked as Outside of Tri-State. 

 

But the IN operator doesn’t have to be used in an IF statement, it can be used in many other places as well, including CASE statements.  Let’s assume that Region was not an included field in the Superstore data set.  We could create a Region field using the IN operator within a CASE statement:

 

CASE [Abbreviation]

 

     WHEN IN('IL', 'IN', 'IA', 'KS', 'MI', 'MN', 'MO', 'NE', 'ND', 

             'OK', 'SD', 'TX', 'WI') THEN 'Central'

     WHEN IN('CT', 'DE', 'DC', 'ME', 'MD', 'MA', 'NH', 'NJ', 'NY', 

             'OH', 'PA', 'RI', 'VT', 'WV') THEN 'East'

     WHEN IN('AL', 'AR', 'FL', 'GA', 'KY', 'LA', 'MS', 'NC', 'SC', 

             'TN', 'VA') THEN 'South'

     WHEN IN('AZ', 'CA', 'CO', 'ID', 'MT', 'NV', 'NM', 'OR', 'UT', 

             'WA', 'WY') THEN 'West'

 

END

 

The alternative to this would be a very long case statement (or joining up to another table completely).  So you can see, the IN operator will be very powerful and I can’t wait to use it.

 

  

2. Relevant Values for Set Controls

 

Set Control is a feature that was released just a few months back in 2020.2.  It allows users to interact with a checkbox list, similar to a filter, to add or remove items to and from the set. Before we get into the improvements in 2020.3, let’s take a quick look at how set controls work.  (I will be specifically speaking about the filter-like menu and not the major improvements to the set action window). 

 

So let’s build a simple viz using Superstore.  Place Sub-Category on Rows and Sales on Columns to build a bar chart. 

 

 

Now let’s click on the Sub-Category field in our list of fields, then click Create à Set.  Name the set Mark Wahlberg.  Just joking, name the set Sub-Category Set (feel free to leave it Mark Wahlberg if you prefer) and add ALL values to the set.  Now place that set on the Color card.  Next, right-click on the Sub-Category pill (that is on Color) and choose Show Set:

 

 

When you do this, a “filter-like” menu will appear on the right side.  This is not a filter, however.  It is a Set Control. 

 

To use the set control, all you need to do is to check the boxes in the list.  If you uncheck a box, it will be removed from the set.  The values will still show, but it will show as OUT of the set rather that IN the set.  This is incredibly powerful stuff. 

 

Okay, so what changed in version 2020.3?  In versions 2020.2 and earlier, there was no way to control the values that were shown in the Set Control (aside from some trickery from Filippos Lymperopoulos – check it out here).  If values were “filtered” out, they would still show. 

For example, in version 2020.2 or earlier, let’s add Category as a filter to this view and set it to Furniture only.  You see that Sub-Categories related to Office Supplies and Technology are filtered out of the bar chart, but those values remain in the Set Control, which can be quite confusing. 

In 2020.3, we can control what is shown in the set control using context filters and the general equivalent of relevant values.  Let’s try it in version 2020.3 using the same example from above.  Go ahead and add the Category filter to Context.  Now on the set control, click the down arrow to access the menu.  You will see that currently, it is set to All Values in Database.  Change that to All Values in Context.  

 

 

When you do this, the Category Context filter will filter the values within the set control as shown below:

 

It may seem like a small feature, but it will make a world of difference when we consider the user experience.  It should be noted that since we are required to add this filter to context, we must be careful to understand how it affects other parts of our workbook through the Tableau Order of Operations.  This will have a direct impact on when Fixed LODs are calculated as they will calculate after the context filter. 

  

3. Export to Crosstab Button

 

In version 2020.1, Tableau introduced the ability to add a button to the dashboard to allow users to export to an image, PDF, or PowerPoint.  In version 2020.3, they added the option to download to a Crosstab.

I love the ability to download to image, PDF, or PowerPoint, but I don't use that functionality very often.  That said, it is very common that I allow users to download tables into CSV files.  In the past, I utilized what Andy Kriebel referred to as the Greatest Tableau Tip Ever (and I agree, it's a really good one), where he showed how to use a bit of trickery to allow the download of a table.  However, with 2020.3, I'll be able to allow this with just a click of a button!  So how does it work?  It's simple. 

Make sure you are on a dashboard and that the dashboard tab is selected.  At the bottom, you will see an option for “Download”.  Click this and drag it to the dashboard.

 

When you do so, you will see the following:

Next, click the down arrow and choose Edit Button.  

The top drop-down will allow you to choose PDF, Image, PowerPoint or Crosstab - choose Crosstab.  From here, you can change the button style much like you can with a dashboard navigation button or a show/hide container button.  I typically like to change the Button Style from a Text Button to an Image Button.  When you do so, you'll be prompted to choose your image.  (If you don't know how to create your own buttons, I would recommend watching my PowerPoint for Tableau Tutorial video or use my Buttons Template). When finished, click OK.

My button now looks like the following:

 

 

When your users click the button, they will receive the following prompt. 

 

 

Users can choose the worksheet in either Excel or CSV format then click download.  It’s that simple!

 

This feature is fantastic as it allows your users to get at the data behind the visualization.  Please note, however, that your users will see all worksheets in the dashboard, so be careful in allowing for this option as you may provide data in difficult to understand formats (or provide data that you don’t want to provide).  You should also name them appropriately; Sheet 1, Sheet 2, etc. won’t work.  As a best practice, I would recommend naming the sheet of interest with a symbol at the beginning so that it appears first on the list.

As a side note, I added an idea to the Ideas Forum suggesting that developers should be able to dictate exactly what worksheets users can download.  If you believe this is a good addition to this feature, please upvote it here: Ideas Forums - Download to Crosstab.

4. Write to Database in Tableau Prep

This new feature commentary comes from Ken.  Please no comments on how much better my writing is than his.  😃

Hey everyone. Ken here. I wanted to make sure to get in on Kevin’s 2020.3 new features. He’s showing you all the great stuff added to Desktop, so I don’t have much to add there, but I do want to talk about my favorite new feature of Tableau Prep Builder—write-to-database. In all prior versions of Prep, you’ve been able to write to an extract file or csv, but 2020.3 introduces the ability to output to a database—currently supported databases are SQL Server, Oracle, PostgreSQL, MySQL, Snowflake, Redshift, and Teradata.

 

As shown below, using a survey of Mark Wahlberg movies, it’s pretty easy to use this new feature. In an output step, you’ll simply choose the “Database table” option, specify the server connection (includes your login credentials), choose a database, then choose a table.

You can choose from three refresh options:

 

1) Create Table – If the table exists already, it will be deleted (dropped) and recreated.

2) Append to Table – Data will be added as new records, while not impacting existing records.

3) Replace Data – Replaces all data in the table. Essentially, this deletes the data then reloads it.

 

In addition, the Custom SQL option allows you to specify a SQL statement to run before or after the output step. For example, you might be writing a daily snapshot of some data and the “current” snapshot is flagged with a Boolean field—the current snapshot is TRUE, while everything else is FALSE. In this case, you might create a “Before” SQL statement to update all the flags to FALSE before you then write the most recent snapshot data, which is marked as TRUE.

 

So, why do I love this new feature so much? The biggest reason is that it creates a lot more use cases for Tableau Prep. In my professional life, my team has broad responsibilities in the analytics space. In the past, we’ve used Prep for one of two primary use cases—1) Preparing data so that it can be visualized in Tableau Desktop (typically written to an extract) and 2) Helping faculty and students to prepare data for research projects (typically written to csv). But my team also spends a lot of time building data models in our SQL Server data warehouse. With the write-to-database feature, Prep can now be used to aid in that process, making data available to data scientists or analysts who may not be using Tableau Desktop for all of their use cases.

 

I believe that write-to-database will help to solve some data governance problems as well. Certain companies and industries, particularly those that are highly regulated, work hard to ensure that data is centrally managed and controlled. With write-to-database, end users can now publish their data directly to a governed database, ensuring complete control over this protected data.

  

5. Clearing Parameter Actions

 

The last feature I’m going to talk about today is Clearing Parameter Actions.  I had been using the beta version of 2020.3 for a month or so before I even noticed this option.  In fact, it doesn’t even show on the 2020.3 Coming Soon page.  I will admit, however, that I am delighted by this as it will be incredibly powerful…and it was a suggestion that I added to the Ideas Forums when parameter actions were initially released.  Okay, let’s check it out.

 

We will use this simple dashboard as our example. 

 

This dashboard shows sales by State in a hex map, sales by State over time in a line chart, and sales by category and ship mode in the form of bar charts.  The hex map is used as a control for the rest of the dashboard.  When a user clicks on a State, the bottom two bar charts are filtered down to reveal values for only that State.  The line chart works a little differently.  When you click on a State in the hex map, nothing is filtered from the view.  Instead, that States line is highlighted in a darker color and the line is made thicker. 

 

To make this happen, I’ve utilized a Parameter for State.  When the State in the chart is equal to the State in the parameter, then (through some calculations) the line is made darker and wider.  I’ve also implement a parameter action so that when you click on a State in the hex map, that State value is sent to the parameter.  (I walked through this exact scenario in my Yay Parameter Actions presentation.  Clicking on the link will take you directly to that example). 

 

Now here’s the issue.  When I click on a State, it filters the bar charts and highlights that line in the line chart.  When I click on a different State, it filters and highlights the selected State.  However, when I click off any State (clear the action), the bar charts revert to showing all values for all States, but the line chart does not.  That is because that last selected State is still listed in the parameter – there was no way to clear that parameter.  Below is what it might look like:

 

 

In 2020.3, we are now able to clear that Parameter Action and set it to a default value.  Let’s give it a go.  First, let’s check out the new parameter action window:

 

 

At the bottom right, you’ll see the new option for “Clearing the selection”.  If we set it to “Keep the current value”, it will act as it did previously, i.e. if I select Ohio, and then clear it, Ohio will remain in the parameter.  However, if we change the option to “Set value to”, we can dictate what value is selected in the parameter when clearing it. 

 

As mentioned before, my parameter lists only the State names in a list parameter.   I added a value of “All States” to this list parameter.  As mentioned before, the calculations dictate that if the State in the chart matches the State in parameter, then it will make the line darker and wider.  Well, the value of “All States” will never match any of the states in the view, so whenever the parameter shows a value of All States, no lines will be darker or wider – they will all be light gray and thin. 

 

(As a side note, I opted to use a list parameter so that we could use it as a drop-down menu elsewhere in the dashboard.  However, that parameter could simply be a string parameter that accepts all values.  In that case, we would not need to “add” a value of All States; it would simply be accepted by the string parameter). 

 

So now, we can set our parameter action to default to this All States value when the action is cleared.  Simply check that second radio button and type the value of All States. 

 

Now when a user clears the action, the parameter value will be set to All States and the line chart will be revert to its initial state.  Check out the GIF below:

 

Man, I love this feature!

 

 

Wrap-Up

 

If you’re a fan of Mark Wahlberg, I mean Tableau; you’re going love these new features.  Let me know what you think of them. 

 

As always, if you ever have any questions or comments, please feel free to reach out to me at any time. 

 

 

Kevin Flerlage, August 12, 2020

Twitter| LinkedIn | Tableau Public