How to use Set Actions in Tableau for drill down

So here we are! 2018.3 is here and with it, along with many new AWESOME features are set actions! And boy, are they gonna change the game. [I realize that we say that with nearly every release but seriously, set actions are gonna be big]

2018.3 was released Monday night and wouldn’t you know it, Ann Jackson was on top of it and dedicated this week’s #WorkoutWednesday challenge to set actions.

Having never dealt with Set actions, this week was a great challenge. It was also really fun because, THIS COMMUNITY IS AMAZING and as soon as new features are released the Tableau Public community jumps head first and creates so much content exploring said features.

So while I struggled to understand the concept I was able to read a couple blogs as well as reverse engineered a couple workbooks in order to reproduce the challenge and more importantly LEARN how to do things

So after much reading, reverse engineering and learning here’s my breakdown of I solved this week’s challenge.

h/t: excellent blog post by Corey Jones

SetAction.2018-11-02 16_30_27

So here’s how I completed this challenge

First thing’s first – create a simple tree map with Category and sales

Screen Shot 2018-11-02 at 3.20.48 PM

The way I made sense of this in my mind is that we’re essentially going to be creating a hierarchy of 3 fields using sets and calculated fields as opposed to the out of the box method in Tableau.

For learning purposes, I like to name my calculated fields in order so that when I or someone else references this workbook it will be easier to reverse engineer. This is something that picked up from Luke Stanke

Step 1

The first thing I did was create an empty set based on the highlest level of drill, [Category] in this case.

Screen Shot 2018-11-02 at 3.41.00 PM

It will make sense why it’s empty as we go along. Stay with me

Step 2

Next we need to create the first of two dynamic dimension fields. They are going to be dual purpose. They will be use label and in tooltips as well as adding the proper level of detail to our treemap. We essentially want to start at [Category], if we drill once, we want see [Sub-category] and if we drill again, we want to see the [Product].

Here’s the first dynamic field:

Screen Shot 2018-11-02 at 3.45.45 PM

What we’re doing here is testing to see if we have any members IN our first set, then return [Sub-category] otherwise return [Category]. *The UPPER() function is there just for formatting.

Step 3

Now because these are “Set Actions” we need to have sets for each drill down. So it’s time to make another set. This time we’re gonna make a set out of this new dynamic field.

Screen Shot 2018-11-02 at 4.05.55 PM

Okay, this time we’re going to select all members. This is why we created our first set as empty. Going back to the dynamic field, because it’s empty, there are no members are in which makes the first part of the IF statement false and returns the ELSE statement.

Step 4

Next we need to create the dyamic field that will return either the [Sub-category] or [Product] name.

Screen Shot 2018-11-02 at 4.16.22 PM

This will look at each set and test for members IN is true then Product else it will fall to the previous dynamic field.

As I stated earlier, we could’ve done this in a nested IF but then we wouldn’t have the Sub-category level of detail available in our tree map. I would go straight from Category to Product. And nobody wants that.

The line chart

Now that we finished up the treemap, we’re ready to tackle the line chart. Believe me, it’s much easier 🙂

Again, say it with me

Set actions require sets.

Every time. So we can see from the example, that we’re only looking at the data at the month level. I’m going to go ahead and make a custom date field at the month date value level.

Now we can create a set from this new field include all members by default.

Now, this is the important part, we need to put this month date set on both of our sheets and set them to “IN” as this will work as the traditional date filter.

Putting it all together

Let’s create our dashboard and start setting up our dashboard actions.

The first thing you’ll notice are two new action types

  • Go to Sheet
  • Change set values

Any guesses on the one we want?

Screen Shot 2018-11-02 at 8.57.09 PM

Set Action #1

New action means new dialog box

Screen Shot 2018-11-02 at 8.59.07 PM.png

  • Name your set action
  • Select your source sheet
    • We’re go in the same order as we built so we select the tree map
  • Target set
    • You have to select your datasource
      • This is fascinating as it implies that the possibility exists to cross datasource set actions
    • Select the set
      • Go in numerical order, right?
    • Clearing the selection will:
      • similar to our dynamic fields and the first set we created; we want to go back all the way to the empty set. Select “Remove all values from set”

Set Action #2

Are picking up on the pattern? We created two sets, two new fields for the tree map…so yep, we’re creating two set actions as well.

Screen Shot 2018-11-02 at 9.23.22 PM

As you can see, we’re gonna follow the same steps as we did for the first step but we’re going to choose the other stop (obviously) and we’re going to select “Add all values to set”

These two set actions together will give our desired result

The month action set

Lastly but not least let’s create one more set for the month date set

Screen Shot 2018-11-02 at 9.32.25 PM.png

And now…

SetAction.2018-11-02 16_30_27

click here for the interactive version

Final thoughts

Like I said up top, I think set actions (and the forth coming parameter actions currently in 2019.1 beta) will change the we build dashboards.

This community is pretty awesome and I encourage you to go check out what else has been done with set actions

There ya have it

GO FORTH AND VIZ!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: