Today DS11 learnt about some of the new Set Actions available in the new Tableau 2018.3. As Harry has this topic for his training sessions back in December, he knows them well (see his blog here). Therefore, Harry took us through some of the more ‘common’ examples, like proportional brushing (see Andrew’s blog for details), drilling down into the data, or recalculating a colour field depending on what was selected in the dashboard or view (see Louise’s blog here) – can you tell that DS11 learnt Set Actions recently?!
At the end of the session, which included both coaches learning and having their minds blown alongside us, was to take one of Lindsey Poulter’s Set Action Dashboards and try to recreate it. We were only allowed to download and look at her work as a last resort. I chose to recreate ‘Change Dimensions’, where set actions are used to split a dashboard up into Categories, Region and Shipping Mode. Lindsey used the US Superstore dataset and I’ve recreated this in the EU Superstore dataset.
Here’s how these set actions were put together:
1. Create the relevant sets
Unsurprisingly set actions require sets! So sets of each of the things you want to cut the view by need to be created. Right click on each of the fields you want, in this case (Category, Region and Ship Mode) and select ‘Create set’ from the drop down menu.
As we want the set to contain no values, don’t select anything within the set yet. Name the set something sensible as you’ll need to reference it later. In this example they’re called ‘[Field] Set.
2. Create Calculated Fields
Similar to parameters, set actions require some calculated fields to talk to the sets. In this example, we need a calculated field that tells the colour or dimension to split by itself by putting some members in the set – i.e. ticking some of the boxes within the sets we created earlier.
These calculated fields are called ‘[Set] Selection’ and hold the formula:
{MAX(IF [Category Set]=FALSE THEN 0 ELSE 1 END)}
There needs to be a calculated field for each of the Sets created in step 1.
3. Create a field to break out the data
Another Calculated Field is needed to bring in the relevant members of the chosen set into view.
Create a calculated field called ‘Breakout’ with the calculation:
‘IF [Category Selection]=1 then [Category]
ELSEIF [Ship Mode Selection] =1 THEN [Ship Mode]
ELSEIF [Region Selection]=1 THEN [Region]
ELSE ‘Overall’
END
4. Create the selection sheet
For this, create a ‘Placeholder’ field. This create spaces for you to rename and detail the different dimensions you’re cutting the view by.
The formula used here is simply:
MIN(1)
Put this onto the column shelf of your selection sheet. Then duplicate it for the relevant times.
Then add the relevant dimension field onto each detail shelf on the marks card for each relevant placeholder. Type out the name of each, format and there you go.
5. Create the two sheets
Line Chart
Now that the Breakout field is created, this can be used on our axis for our other sheets.
For the line chart: put SUM([Sales]) onto the row and Breakout onto columns, as well as onto colour.
To make this super clear for the users, also create a calculated field that enables the title of the line chart to be dynamic depending on the category selection. The formula is:
IF [Category Selection]=1 THEN “by Category”
ELSEIF [Region Selection]=1 THEN “by Region”
ELSEIF [Ship Mode Selection]=1 THEN “by Ship Mode”
ELSE ”
END
IMAGE
Big Numbers (BANs)
For the big numbers, put Breakout on columns and then duplicate it onto colour and text. Also add SUM([Sales]) onto text. Format the text so that the SUM([Sales]) is larger.
And voila! You can now dive into category specific information on one dashboard!
See my version of this set action in a workbook here to download and have a play!
Or click play on the below gif to see the set action ….. in action!