Tableau Set Control Part 3: Use Cases (Continued)

 

In the first part of this blog series, Tableau Set Control: The Basics, I shared some of the history of sets then introduced the set control and its most basic use case, simple filtering. In Tableau Set Control Part 2: Use Cases, I addressed the first two categories of use cases, Filtering without Filters and Multi-Select Parameters. In this blog, I’m going to address the final two categories—Compare Populations and Listing Filter Selections. This blog will refer back to content and examples from the first two blogs, so if you haven’t read them already, I’d suggest going back and reading them first.

 

3) Compare Populations

Another great use case for set control is comparing multiple populations. Let’s take a look at two examples.

 

3a. Compare In vs Out

The simplest way to compare populations is using the set’s built-in In/Out functionality. Let’s say that we want to compare a selection of sub-categories to everything else. We can start with a line chart like this:

 

 

We create a set on Sub-Category then add that to the color card. Then we right-click that pill and show the set.

 

 

This will now compare the items that are in the set (Office Supplies and Technology) in red and those that are out of the set (Furniture) in blue.

 

But, one issue I have with this is that, in order to read the chart, you have to first observe the color then look to see what’s in/out of the set. It would be easier if we could do something like this:

 

 

Of course, the title would have to be dynamic so that the comma-separated lists adjust based on the makeup of the set. The good news is that this is definitely possible! But this example fits better in the last category on this blog, so we’ll come back to it shortly.

 

3b. Compare Completely Different Populations

The In/Out method works great if you don’t want any overlap in your two populations. But what if you want your user to specify two or more different populations which could include the same items? For example, perhaps you want to be able to compare two groups of customers. The first group should include all customers with names starting with A through E and the second should include all customers with names starting with C through I. In this case, customers with names starting with C, D, or E would appear in both groups. We cannot easily do this using the base In/Out functionality of sets, but we can leverage multiple sets. We’ll start by creating two sets on Customer Name—I’ll call them Customer Group 1 and Customer Group 2. We’ll add both of those as In/Out filters with the “Use All” option, then well show the set control. Next, we’ll create separate sales measures for each group of customers.

 

Group 1 Sales

// Sales for customer group 1

IF [Customer Group 1] THEN

    [Sales]

END

 

Group 2 Sales

// Sales for customer group 2

IF [Customer Group 2] THEN

    [Sales]

END

 

Then we put everything together using Measure Names and Measure Values.

 

 

Your user can now use the two sets to specify the unique makeup of each group, regardless of whether or not there is any overlap between the two.

 

Note: For a great practical example of using set control to compare populations in survey data, check out Steve Wexler’s blog, Set Controls and survey data – how to compare responses for this group vs that group vs overall.

 

4) Listing Filter Selections

From my work on the Tableau Community Forums, I’ve come across many scenarios where someone wants to be able to list the values that are selected (or not selected) in a filter. While sometimes possible with a filter, it can be difficult and is fraught with potential problems. Parameters work great for these situations, but they only work when you want a single value. However, since sets can be used in calculated fields, we can use them to get a list of the items that are in or out of them.

 

4a. Simple List of In/Out Values

Let’s start with a simple example of how we might use this. Let’s return to the In/Out analysis from 3a above.

 

 

Remember that we’d ideally like to be able to show those items that are in the set and those that are not. We could do this by creating a sheet with the Set In/Out on the columns shelf and on the color card and Category on the text card.

 

 

We can then add that to a dashboard with the chart.

 

 

This is a big improvement as we can clearly see the makeup of the two different lines. And the list is dynamic so it will automatically update as we change what is in and out of the set.

 

4b. Comma-Separated List

While the above is a great start, I prefer to make the title itself dynamic, listing the In and Out as comma-separated lists. This requires a bit of table calculation trickery, which I learned from Jonathan Drummey’s The Next N Table Calculations workbook. It the workbook, Jonathan demonstrates the use of PREVIOUS_VALUE to brilliantly create comma-separated lists. We have to slightly modify the technique for sets, starting with calculated fields to get the values In and Out of the set.

 

In

// Category only if it's in the set

IF [Category Set] THEN

    [Category]

ELSE

    ""

END

 

Out

// Category only if it's not in the set

IF [Category Set]=FALSE THEN

    [Category]

ELSE

    ""

END

 

Then we employ Jonathan’s technique, creating comma-separated lists for both In and Out.

 

List In

// IN comma-separated list

IF FIRST()==0 THEN

    MIN([In])

ELSEIF MIN([Category]) != LOOKUP(MIN([In]),-1) THEN

    IF PREVIOUS_VALUE("") = "" OR MIN([In])="" THEN

        // Don't add the comma

        PREVIOUS_VALUE("") + MIN([In])

    ELSE

        PREVIOUS_VALUE("") + ", " + MIN([In])

    END

ELSE

    PREVIOUS_VALUE("")

END

 

List Out

// OUT comma-separated list

IF FIRST()==0 THEN

    MIN([Out])

ELSEIF MIN([Category]) != LOOKUP(MIN([Out]),-1) THEN

    IF PREVIOUS_VALUE("") = "" OR MIN([Out])="" THEN

        // Don't add the comma

        PREVIOUS_VALUE("") + MIN([Out])

    ELSE

        PREVIOUS_VALUE("") + ", " + MIN([Out])

    END

ELSE

    PREVIOUS_VALUE("")

END

 

Now we build a sheet like this:

 

 

Next, we add the In and Out lists to the text card, then we click the text card to edit it. We make sure both fields are on the same line and change the colors.

 

 

We now have this:

 

 

The last thing we need to do is isolate the text on the final row so that we can use it as a title. We’ll start by right-clicking each of the pills on the rows shelf and deselecting “Show Header” to hide them.

 

 

Next we’ll use the LAST function to identify the last row. LAST basically counts the rows in the partition from the last row back. So, the last row will be 0, the next to last will be 1, etc. (There is also a FIRST function which does the opposite, counting from the first row down).

 

Last

// Is this the last record in the partition?

IF LAST()=0 THEN

    "Keep"

ELSE

    "Hide"

END

 

We’ll use this as a filter keeping only “Keep” values. And viola, we have now completely isolated the text.

 

 

Finally, we add that sheet to our dashboard using it as a title.

 

 

Note: The calcs I’ve used are slightly modified versions of Jonathan’s “Feeder” calculations. Because I’ve filtered the data down to just the last row, we don’t need the final set of calculations in Jonathan’s workbook.

 

Wrap-Up

There are, of course, a number of other use cases not mentioned in this series which might not nicely fit into any of these categories. For a really cool example, be sure to check out Kevin’s technique for creating OR filters: Change Filters From "AND" to "OR". That said, I hope that this series helps you to recognize some of the types of scenarios where set control can help to solve some of your more complex problems. Once you get the basics of using set control in this manner, you’ll start to see applications for its use all over the place.

 

Thanks for reading! If you have any questions or comments, please let me know in the comments.

See the companion workbook here: Tableau Set Control Use Cases

 

Ken Flerlage, November 14, 2022

Twitter | LinkedIn | GitHub | Tableau Public