The English word “set” has more than 400 meanings in the dictionary. I am not sure if it was by design, but I would choose no other word to describe one of the most underrated and versatile features in Tableau: Set.
When you drag it in the Filters pane, it acts as a good old dimension filter showing selected or deselected values. When you drag it in the Colors section of the Marks card, you can color the selected and deselected values. You can even combine it with another set.
Additionally, you even get a whole host of unique options when you configure a set action. But all this pales in comparison to the flexibility you will have when you start using sets in calculations.
In this blog, I will be walking you through one of my favorite use cases of set in a calculation (which I have personally used to great effect in multiple scenarios) by walking you through my solution to the Workout Wednesday challenge that I had the pleasure of sharing for WOW2023 – Week 32.
Dataset
To follow along, you can use the Orders table of the latest (2020-2023) Superstore dataset. Alternatively, you can also use the dataset on the WOW challenge link.
Step 1: Create the Customer Details Table
Drag Customer Name to the Rows Shelf and then drag Sales and Profit to the Columns Shelf as shown below.
It is often a good idea to set the default formatting for the Profit and Sales fields right off the bat so that you don’t waste time fiddling with it later on.
When you want to Label a view with the measure that is used to create the view, you can simply tick on “Show Mark Labels” from either of the two places shown below.
This will let you color the two bars separately.
Rename the sheet to Table and format it to your liking so that everything looks good. Add banding, zero lines, increase the size of rows, etc., and you will end up with something like this.
Step 2: Setup the Actions
You need to store two things when any bar in the Customer Details table is clicked: Measure and Customer Name.
For getting the measure, a parameter is the best option. Create a parameter as per the following image.
You can directly add the Measure Names as the Source Field since it was there in the Color of the Marks card. Since the names of the measures are the same as the options in the parameter, you can directly use the Measure Names instead of creating some dummy calculations for getting the Profit/Sales text. Try it out!
This technique is very versatile. It even works with an alias for a placeholder field since that is also technically a measure name. To test this out, create a new sheet, double-click on the rows, and type 1 in the blank pill to create a placeholder.
You can use this technique to easily populate a parameter from a clickable KPI sheet created using placeholders.
Next, you need to somehow capture the Customer Name and use it to show the values corresponding to that customer in the Bar Trend chart. There are a few ways you can do this.
Firstly, you could set up an action filter or a regular filter (using a set/parameter) that filters the Bar Trend chart to the particular customer. The problem with that is that for customers who do not have orders in all 16 of the 16 quarters, all 16 quarters will not be there in the view.
This happens because you have essentially filtered out the rows containing the order dates corresponding to these quarters when you filter for a customer. You will have to join or create a relationship with your table to another table that has all the quarters you need and do a join/relationship/data blending to get all quarters for all customers.
Your second option may be to use a parameter with parameter actions. Although it is possible to solve the challenge using a parameter, there are a few caveats if you were to use it in other scenarios.
Only one Customer Name can be selected at a time. You can use the multi-select parameter option to solve this, but that will cause a huge hit on performance as it uses string functions.
You cannot search for a specific Customer Name. You will have to scroll through the entire list of customers to find the customer you want. If you use the Type-In option with the parameter, you will have to remember and type in the Customer Name you want to see.
Parameters are not designed for a use case involving hundreds of options and will slow the performance down.
So, if parameters are not the best way to go, what is? Sadly no points for guessing this one: sets.
Right-click on Customer Name and create a set, as shown below.
Since only one customer needs to be selected at a time, you can check “Single-select only” and select “Assign values to set.” Also, when the selection is cleared, select “Remove all values from set.”
Click on different values and watch how the parameter and set get updated based on your selection.
Now you may have noticed that whenever you select something, Tableau grays out everything else, which does not look good.
// Highlight Disabler
"x"
Drag this in the Detail of the All Marks card.
Another advantage of using sets is that you can easily move the selected customer to the top of the pile irrespective of the Sort order you select. To do this, just move the Customer Name Set before the Customer Name pill.
Since this is not needed for the challenge, reverse the order of the pills to the original.
To summarize, whenever you are in a situation where filters are not useful and you want to select single or multiple options from a long list of values from a particular field, it is better to use sets. You get all the goodness of dimension filters like single-select, multi-select, search, etc., with a set.
Parameters should be used when your list is small and/or is not created from values from a particular field. Also, when you want something to get passed across data sources or when you are dealing with selections related to dates, use parameters.
Step 3: Build the Bar Trend Chart
To start with this, first create a calculation, as shown below, that returns the right measure.
// Bar Measure Selected
IIF([Bar Measure Selector] = "Sales", [Sales], [Profit])
Since there are only 2 options in the Bar Measure Selector parameter, an IIF statement will suffice. If there were more than 2, use CASE statements.
Now, create a new sheet called Chart, right-click, drag Order Date in Columns, and then double click (an alternative to single click to select and click Ok at the bottom of the menu) on the Green Quarter (Order Date) as shown below.
Convert this pill to Discrete. Then drag the Bar Measure Selected calculation in the Columns shelf and change the Mark type to Bar. Also, show the Mark Labels. You will get this.
Since either the Profit or the Sales bars will be visible at a time, you can directly drag the Bar Measure Selector parameter in the Color of the Marks card and then select the same colors as the Customer Details Table to color these bars.
To show the trend for a particular customer when clicked, modify the Bar Measure Selected calculation as follows.
// Bar Measure Selected
IIF([Bar Measure Selector] = "Sales", [Sales], [Profit])*IIF({MAX([Customer Name Set])},INT([Customer Name Set]),1)
When a set is added to a calculation, it acts as a boolean field returning True for the rows which are there in the set and False for the others. In this case, the Customer Name Set will return True for all the rows with the Customer Name that is in the set.
{MAX([Customer Name Set])} will return True when a customer is selected, i.e., the set is not empty. When this condition is True, INT(Customer Name Set) — the “then” part of the IIF statement — will be returned. Wrapping a Boolean inside an INT will return 1 when True and 0 when False.
So basically, if the set is not empty, only the Sales or Profit corresponding to the selected customer will be non-zero and thus get summed up. When the set is empty, {MAX([Customer Name Set])} will return False, 1 — the “else” part of the IIF statement — will be returned. Thus, all the orders will get considered when no customer is selected.
Note that you could have also configured the Set Action to have all values added to it by default. Then there is no need to check if the set is not empty. But I avoid this (even though I am not sure which would be more performant) as having all values in the set for getting the overall trend would mean Tableau has to evaluate the boolean condition for every row.
When a single customer is selected in the set, this is what you will get.
Take a pause and think about this for a moment about what exactly happened here. You are plotting the values of one customer on a view that is at the lowest level of granularity (as there are no filters applied.) You can use this idea to solve some other problems in Tableau too. Check out scenario 4 from this blog for another application of this technique.
Can you guess what will happen if you add the set in Filters?
As explained in the previous step why action filters or filters of any sort will not be useful here; it only shows the non-zero values as there are no orders in 13/16 quarters for this customer.
Even if you Show Missing Values in the Quarter pill, you will only get the quarters between the first and last quarter the customer placed orders.
The only other remaining part is the title of the bar chart, which can be created using the calculation shown below.
// Bar Measure Title
SPACE(20) + "Trend" + {MAX(IIF([Customer Name Set]," for Customer: " + [Customer Name],""))}
The function Space(20) will add a blank space of 20 character lengths. This will be useful later. The last part checks if the Customer Name Set is not empty and adds “for Customer: ” [Customer Name] otherwise, a “”. Wrapping the last part in {MAX()} ensures that we only get one value.
Add this in the Tooltip of the Marks card and then Insert in the Title.
Step 4: Putting it all Together
In a new dashboard, add the two sheets: Table and Chart. Resize them so that it looks proper, and then change the Set, Parameter, and Highlight Action’s Source and/or Target Sheets to run from the dashboard (with Chart unticked) instead of Table as shown below.
And Boom! You are all ahem… set.
Conclusion
Hopefully, you enjoyed learning this novel way of using sets in a calculation and all the other tips with this detailed walkthrough of the Workout Wednesday challenge. I hope you have some newfound respect for this versatile love child of Parameters and Filters. Start using it more in your work and come up with your own crazy use cases.
If you want more information on Sets in Tableau, contact our team of experts!
