Following on from my recent webinar on set actions in which I was unable to finish the whole exercise to drill down two levels in a scatter plot I mentioned there would be a blog post to explain this very soon, so here it is! This also coincides nicely with my first blog post ‘Set Actions: Exploding Bubble’. From my first blog post I was unsure how to drill down to the next level of granularity in the data while excluding the other values not in the set which you have chosen to drill down to e.g. you click the ‘Central’ region > you are then given the states only in the ‘Central’ region > click on ‘Illinois’ state > only given the cities in ‘Illinois’. The final interactivity should look like below:

Create the First Level of Drill Down

Using Sample Superstore:

  1. Create the view: SUM(Sales), SUM(Profit) with ‘Region’ on Detail
  2. Right click ‘Region’ in the dimensions shelf and select Create > Set…
  3. Rename the set as: [1. Region Set] (this will help when things get complex..)
  4. Create calculated field:
    [2. Region to State]

    IF
    [1. Region Set] = TRUE
    THEN
    [State]
    END
  5. Bring [2. Region to State] to detail under [Region]
  6. Add the worksheet action as ‘1. Region Set’ as the target set. Select ‘Remove all values from set’ when clearing the selection

Create the Second Level of Drill Down

  1. Right click [2. Region to State] in the dimensions shelf and select Create Set…
  2. Rename the set as [4. State to City Set] (we will come back to 3. named calculated fields)
  3. Create calculated field:
    [5. State to City]

    IF
    [1. Region Set] = TRUE
    AND
    [4. State to City Set] = TRUE
    THEN
    [City]
    END
  4. Bring [5. State to City] to detail under [2. Region Set]
  5. Add the worksheet action as ‘4. State to City Set’ as the target set. Select ‘Remove all values from set’ when clearing the selection (similar to 6. from above)

This will allow the both levels of drill down but will not filter the other values in the data set. E.g. if I put ‘Region’ to color on the marks shelf, you will see that the other regions are present after the first drill down. When I click on the South region, I only want to see the states in that region and want to exclude the other regions to the right of the scatter plot. Then, if I was to click on the state of Virginia I would only want to see all cities in Virginia.

Create the filter between the levels of drill down

I found the easiest way to explain this was to see how Tableau reacts as you click through the sets. First bring the [1. Region Set] and [4. State to City Set] onto the rows shelf (As we click through the set actions we be able to see the different combinations of the two sets at each level. E.g. at the Region, State and City Level)

  1. No set action is active:

Both sets are Out (this FALSE in Tableau syntax when we put it into a calculated field just to make things extra confusing!) and we are the Region Level

([1. Region Set] = FALSE AND [4. State to City Set] = FALSE)

2. The [2. Region to State Set] is active through the Region to State Set Action

At the Region Level ([1. Region Set] = FALSE AND [4. State to City Set] = TRUE)

At the State Level ([1. Region Set] = TRUE AND [4. State to City Set] = FALSE)

3. The [5. State to City Set] is active through the State to City Set Action

At the Region Level ([1. Region Set] = FALSE AND [4. State to City Set] = FALSE)

At the State Level ([1. Region Set] = TRUE AND [4. State to City Set] = FALSE)

At the City Level ([1. Region Set] = TRUE AND [4. State to City Set] = TRUE)

4. Create the calculated field: [3a. Filter P1]

We need to create a calculated field for all these combinations at each level of the drill down from what we have seen from above:

[3a. Filter P1]

// This filter determines the granularity //

// Set Combinations for Region //

IF
[1. Region Set] = FALSE
AND
[4. State to City Set] = FALSE
THEN
1

ELSEIF
[1. Region Set] = FALSE
AND
[4. State to City Set] = TRUE
THEN
1

// Set Combinations for State //

ELSEIF
[1. Region Set] = TRUE
AND
[4. State to City Set] = FALSE
THEN
2

// Set Combinations for City //

ELSEIF
[1. Region Set] = TRUE
AND
[4. State to City Set] = TRUE
THEN
3

END

5. Create the calculated field: [3b. Filter P2]

In order to exclude the levels not wanted in our drill down we create the calculated field. This will be the maximum value for the current level of drill down we are in:

[3b. Filter p2]

{FIXED : MAX([3a. Filter P1])}

6. Create the calculated field: [3c. Filter P3]

[3a. Filter P1] = [3b. Filter p2]

Drag [3.c Filter p3] to the filter shelf and select TRUE. As you drill down through the levels you will not only see those in the level above selected. E.g. this works as when no set action is active, we want to see the Region level at this point, [3a. Filter P1] = 1 and [3b. Filter p2] =1 therefore this is true. If you put [3a. Filter P1], [3b Filter p2] and [3c. Filter p3] on to the rows shelf and click through the set actions similar to above this should help with the understanding.

7. Create the label calculated field: [6. Labels]

[6. Labels]

IF
[3a. Filter P1] = 1
THEN
[Region]

ELSEIF
[3a. Filter P1] = 2
THEN
[State]

ELSEIF
[3a. Filter P1] = 3
THEN
[City]
END

8. Drag [6. Labels] to ‘Label’ in the Marks shelf and drag [3a. Filter P1] to ‘Color’. As you drill down the Regions, States and Cities will now be labelled and the colour will change through each level!

Hopefully this post has helped with an intro to drill down set actions when using scatter plots! If you wish to find out more about set actions, check out the webinar here as well as downloading the workbook here showing the drill down from above and other use cases for set actions.