For last week’s #MakeoverMonday on “Ranking the States by Fiscal Condition” I created a bump chart showing the total net assets per person for each state based on each state’s population. The curvy bump chart was created using Kevin Flerlage’s excellent Excel template, which you can read all about in his detailed blog post here ⇨ bit.ly/BumpChart.
This post will focus on the selection panel to the left of the bump chart that allows you to select a state (or states) and have that state highlighted in the bump chart, which was created with a couple of formulas and a set action.
Add State to the View
First off, I added State to the Label shelf:
Create Set and Add to Colour
Then I created a set based on the State dimension, which I called “s.state”.
Side note: I use a naming convention where I start all sets with "s." and all parameters with "p." as I find this helps avoid confusion when using them inside calculations and also makes them easier to access from calculations - e.g. if I type "p." in a calculated field, all of the parameters I have made in the workbook will be listed.
I added this Set to the Colour shelf, which then highlighted the one state that was IN the set:
Notice that Alaska has moved to the top left, in front of Alabama, as those states in the set are being shown first. I don’t want the states to re-order but that will be fixed as part of a later step.
Create a Set Action
I want to be able to click on any state to highlight it, so to do this I create a Set Action to dynamically update the set based on a selection:
Now when I make a new selection of one or more states, that new selection is highlighted:
Change the Layout to Two Columns
Currently, Tableau is determining the layout of the states in the view, from top-left to bottom right, but I want them to be listed in two columns.
To do this I created two calculations. There are 50 states in the data set, so I want two columns of 25 states. The first calculation uses the modular function (%) to ensure the rows go from 1 to 25 (or 0 to 24 to be exact) based on an Index() calculation.
( INDEX() - 1) % 25
This results in the following layout:
Next, I need a Column calculation to fix the layout with two columns:
INDEX() > 25
This will return False if the state number (from the Index function) is less than or equal to 25 or True if it is more than 25, thus creating two columns:
This has ensured I now have 25 rows and 2 columns, but has revealed another issue. Because I am encoding colour with a discrete field as opposed to a continuous measure the colouring of the four selected states (that are in my “s.states” set) is running over into the surrounding cells.
Fix the Colour Encoding
To resolve this I used a tip from Ryan Sleeper, which you can read about in more detail here. In summary, adding another dimension to the Rows and Columns will force any encoding of colour to remain within the cells of the table. For the dimension we can use an inline calculation of two quote marks (i.e. an empty string) “”, which gives the following result:
Reuse the Set to Colour the Bump Chart
The great thing about sets is that, once created, they can be utilised in other views to encode or change the view in a variety of ways – e.g. colour, size or for filtering and sorting. Take a look at my blog post on Set Actions to learn more.
So, to complete this visualisation, I will add the “s.states” set to the Colour card in the bump chart view, and – after selecting the required colours for IN and OUT – the selected states are highlighted:
The interactive dashboard is on my Tableau Public page here.