A while back I completed a Workout Wednesday on Sales by State Analysis: a challenge set by Andy Kriebel to choose a state from a list and show which states ranked similarly in terms of their sales (http://www.workout-wednesday.com/2019-week-19-sales-by-state-analysis/). I'm not going to lie, I found this pretty difficult as I slogged away making various calculations in Tableau, inevitably overcomplicating things whilst failing to make use of the very useful lookup() function.
Using this as inspiration for the following blog post, below is a quick tutorial on how to use a simple set action and a couple of calculations to show the similar ranked states by sales, with the user selecting which states to show in the list. For this I used Sample - Superstore data; the end result we are aiming for is shown in the below gif.
Step 1 - Setting up the view
First of all, I always find it useful to set up the charts/graphs you are going to use. Starting with the map, double click on State which will automatically load Longitude and Latitude into the shelves and a map will appear in the view, then drag Sales onto colour. Then create a new sheet and drag State to the Rows shelf and Sales to the Columns shelf as shown below.
Step 2 - Creating the State Set
Tableau defines Sets as 'custom fields that define a subset of data based on some conditions'. To help understand how Sets work, it is good to consider that items are either In or Out of a Set. Sets are a really useful aspect of Tableau and can be used in a variety of ways to increase user interactions. To learn more about Sets there are loads of useful blogs by The Data School plus information on Tableau's website (https://www.thedataschool.co.uk/blog/ ; https://help.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm)
Creating a State Set is really easy: right click on State, then click Create -> Set, keep the name as State Set, select Alabama (this will help us out later) and click ok.
Step 3 Creating your calculated fields
First of all, create a calculated field called Rank of Sales using the formula below:
Rank of Sales should then be converted to a discrete value by right clicking and selecting Convert to Discrete. Next create a calculated field called Similar Rank as shown below:
This calculation looks at which states are In the State Set, and then returns True for rows that are 1 or 2 records above and 1 or 2 records below that particular record (these are chosen by the 2,1,0,-1,-2 values in the Lookup() function). False is returned for all other rows that fall outside ±2 rows from the State Set.
Step 4 - Completing the bar chart view
Drag Rank and State Set to the Rows shelf which will give the rank of each state according to the sum of sales there, and will show the states which are In and Out of the State Set.
Finally, drag Similar Rank to the Filters card and select True. This will give show you Alabama and the four other states which have the most similar sales.
Step 5 - Create dashboard and dashboard Set action
The final step is to create a dashboard and drag the map and bar chart on - as shown in the first gif - and then set up the Set action. The Set action will allow the user to select a state from the map, which will then add that state to State Set and show the similar sales!
First click on Dashboard in the toolbar -> Actions... -> Add Action -> Change Set Values. Then select Map as the source sheet and State Set as the target set, and click ok.
With a bit of formatting (labelling the bar chart, hiding the State Set headers and colouring the bar chart) the dashboard will be complete!
As is always the case when using Tableau, it is important to have a play around with the calculations and the Set actions because the functionality changes drastically depending on what selections you make. This will help you understand how they work so these techniques can be repeated using other data!
