The challenge of the 49th week of #WorkoutWednesday was inspired by a beautiful Viz that Luke Stanke and Spencer Baucke prepared at the beginning of the year to compare the growth of the Stock Market among the last presidents.

One of the techniques they used in their Viz, allows the user to select a particular president from a grid, and then show it above the grid in order to highlight it. In this challenge we will work with this technique, but instead of selecting a president, a State will be selected to be highlighted.

Challenge Requirements:

  • Show the Top 26 of the states with the highest sales, spread over two sheets:
  • Sheet 1 (at the top): Only the last State selected by the user will be displayed. The state map must be displayed with the name and amount of your sales. In addition, within the map, a circle graph should be included with the detail of sales by cities.
  • Sheet 2 (at the bottom): In a grid of 5 columns, the remaining 25 states of the Top 26 will be displayed. The grid must include the image of each of the states accompanied at the bottom with the name of the State, rank and the total sales amount.

Next, I share the steps I followed.

STEP 1. CREATE PARAMETER TO SAVE THE LAST SELECTED STATE.

The parameter will be originally assigned the name of one of the Top 26 States, in this case I selected “Texas” and later we will define a Parameter Action to allow the user to update its value when interacting with the Viz.

PARAMETER TYPE ALLOWABLE VALUES CURRENT VALUE
State Selected String All Texas

STEP 2. FOR GRID GRAPHIC, CALCULATE THE ROW AND THE COLUMN TO LOCATE EACH OF THE STATES.

For this calculation, it’s very important to remember one of the requirements that, within the grid, for each of the images of the State, information on the name of the State, rank and total sales is included.

To achieve this effect, I will use two different values in the axis of the rows for each of the States: 1) A value to locate the map of each State and 2) A second value to locate the data or text displayed in each State.

For the calculation of the positions within the grid to locate each State, we will use in the formula the value of 5, since it is desired to use 5 columns to show the States.

Column FLOAT((INDEX() – 1) % 5)
Row for Map INT((INDEX() – 1) / 5) * 1.0
Row for Text [Row for Map] + 0.4

Why am I using the FLOAT function and / or values with decimals in the formulas? It’s a way for Tableau to automatically identify that the defined variable is of Data Type: Number (decimals).

Why do I need to work with number type data with decimals? 1) To be able to adjust at the level of decimals where each axis starts and ends and 2) To manage at a decimal level, the distance between the map or image of the State and the location of its text at the bottom.

STEP 3. CALCULATE NEW SALES RANK DIMENSION.

Rank RANK_UNIQUE(SUM([Sales]))

STEP 4. CALCULATE NEW DIMENSION TO IDENTIFY IF IT’S THE STATE SELECTED BY THE USER OR ANOTHER STATE.

When the State is equal to the last State selected by the user, its value is TRUE.

Hide [State] = [State Selected]

STEP 5. CREATE A GRID VIZ WITH THE NON-SELECTED STATES.

In the definition of the Viz two types of graphics will be used: “Map” and “Text”

First, a filter will be included by the “State” field selected in the Top 26 of the States according to the value of their sales.

Move to shelves of:

  • Columns ⬅   “Column”
  • Detail ⬅   “State” sorted descending by Sales
  • Rows ⬅   “Hide”, “Row for Map” & “Row for Text”

For “Row for Map” select type of “Map” chart and indicate Opacity = 0% and include a red border line.

For “Row for Text” select type of graphic “Text”.

Adjust axes:

  • Select “Row for Text” – Dual and Synchronized Axis.
  • Select the axis of “Row for Map” and set in a range between -0.5 and 4.5. Also, indicate the “Reversed” option for the scale.
  • Select the axis of “Column” and set in a range between -0.5 and 4.5.
  • Hide both axes.

For the “Text” graphic, add to the “Label”

  • Label ⬅   “State”, “Rank”, “SUM(Sales)”

It’s important to highlight that the inclusion of the “Hide” variable in the graph allows us to separate the Top 26 States into two groups: 1) True Group – Contains only the last State selected by the user and 2) False Group – Contains the 25 States remaining.

Since we only want to show group 2 in the Viz, we must hide the mark whose value is equal to “True” in the “Hide. For which, the value of “True” is selected with the right click and the “Hide” option is selected from the menu.

Why do we use the Hide option to hide the State selected by the user and don’t filter the data from the beginning to work exclusively with the 25 States of the graph? To solve one of the main challenges in the challenge, which consists in calculating and displaying the Rank of the Top 26 States, knowing in advance that the 26 States aren’t being deployed.

The “Hide” option allows us to define the values ​​defined as hidden in the Table Calculations if required, unlike when we use the filter option, where the filtered values ​​are always excluded from the Calculations of Table.

Next, we will select the “Hide” dimension of the row shelf and with the right click deselect the “Show Header” option. This in order to hide the column.

In Viz, four variables that require Table Calculations are used, but due to their definition requirements we can group them into two sub-groups.

GROUP
Column

Row for Map

Row for Text

The Index function is used.

A consecutive value (1-25) must be assigned to the displayed states. This value will help us to correctly calculate the position that corresponds to each of the States within the matrix or grid.

Rank Rank function is used

The value of the Rank of the State must be assigned according to the level of its sales with respect to the rest of the States (Possible values from 1 to 26).

The definition of the Table Calculations allows us with a few simple changes in the definition, it’s possible to cover the needs of the two groups:

Picture2

Let’s test, for example, what the values for the Top 6 would be, having the State of “Texas” selected according to the definitions of Table Calculation used.

Picture3The definitions used, allow the Index to be reset in each of the values of the “Hide” dimension, while in the Rank its value is assigned consecutively to each State according to its sales level, regardless of its value in the “Hide” dimension.

STEP 6. CREATE A VIZ FOR THE SELECTED STATE.

In the definition of the Viz two types of graph will be used: “Circle” and “Map”

Move to the shelves of:

  • Columns ⬅   “Longitude”
  • Rows ⬅   “Hide”, “Latitude” & “Latitude”

For the “Circle” graph add on the shelves

  • Detail ⬅   “State” & “City”
  • Color ⬅   “State”
  • Size ⬅   “State”

For the “Map” graph add on the shelves

  • Detail ⬅   “State”,
  • Label ⬅   “State”, Rank & SUM(Sales)

Next, we will proceed to

  • Select the second axis and apply Dual Axis.
  • Hide in the Viz, the marks with “False” value of the “Hide” variable, to leave only the selected State.
  • From the shelf of the lines select the “Hide” dimension and with the right click deselect the “Show Header” option.
  • Adjust the Table Calculations of the Rank calculation, using the same definition that was used for the Rank in step 5.

STEP 7. DEFINITION OF PARAMETER ACTIONS.

Create a dashboard that includes the graphs of the previous two steps.

Create a Parameter Action using the “Change Parameter” option of the “Actions” menu, within the newly created dashboard menu, to update the value in the parameter with the user selection when interacting with the Viz

PARAMETER ACTION Source Sheet TARGET PARAMETER FIELD OR VALUE
Parameter1 Name assigned to the Sheet in step 5 State Selected State

Ready!

The latest version of my challenge is here.

Picture4

SUMMARY

Let’s review some of the main lessons that this challenge left us:

  • To be able to calculate the Rank of the sales of each State, independently of quantity of States shown in the graph.

To solve it, we use the option of “Hide” a subset of the data, so that the values ​​of the sales of the hidden States are also considered at the time of calculating the Rank Table.

  • Show within a Trellis-Chart, the image of each State and the information associated with each State such as: the name of the State, rank and total sales.

To achieve this effect, we use two different values ​​in the axis of the rows for each of the States: 1) A value to locate the map of each State and 2) A second value to locate the data or text displayed in each State.

  • How to adjust the range of axes to decimal level.

To achieve this effect, we need the measures used in the axes to be of data type: Number (decimal).

I want to take advantage that today is the last day of the year 2019 to thank you for sharing this 2019 with me and wish you:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

An excellent year 2020 with a lot:

Happiness, Love, Health, Peace &

that we continue to share with Tableau

many new and fun challenges and learnings.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

˛¸.o•°“°•o.˛¸˛.o•°★°•o.˛¸˛.o•°“°•o.˛¸
:█ WELCOME █ WELCOME █ WELCOME █: to the

New Year 2020
˛¸.o•°“°•o.˛¸˛.o•°★°•o.˛¸˛.o•°“°•o.˛¸

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).