For week 19 of #WorkoutWednesday a guest post was held, as Lorna says“of the one and only Andy Kriebel”.
Of course, Andy’s challenge does not lack the use of Table Calculations. Fun and Suffering assured!
Lorna tells us that the challenge was originally created for the DataSchool gym, inspired by Lindsey Poulter’s incredible use examples of #SetActions.
The exercise consists of:
– Create a list of States ordered by Sales, in which the user must be allowed to select a State. When selected, the State must change place and appear in the first position of the list and it will be displayed with a different color to the rest of the States.
– In addition, 3 partial lists must be added with the Sales of each Segment: “Consumer”, “Corporate” and “Home Office”. In each list, only 5 lines should be displayed, one for the selected State and the other four with similar States.
Next, I share the steps that I followed.
STEPS FOR THE DEFINITION OF THE MAIN CHART
Step 1: Create a Set associated with the variable “State”.
To create the Set, select the “State” variable and with the right click, choose the “Create Set” option and name it “State Set”.
Select one of the States in the Set: For example: “Pennsylvania”.
Step 2: Calculate the unique Rank of each State.
Based on the total sales of the State, a Rank_Unique will be calculated.
Step 3: Create the main chart on a sheet with the name “Principal”.
Move to the shelves:
- Rows ⬅ “State Set”. Moving it will become “IN / OUT (State Set)”
“Rank”. Convert with the right click to Discrete
- Text ⬅ SUM(Sales)
The inclusion of the field “IN / OUT (State Set)” is intended to always show the State selected by the user in the first position of the list. The second field, the “Rank” will serve to order the rest of the States from highest to lowest level of sales.
Hide the “IN / OUT (State Set)” field so it’s not displayed.
Step 4: Define the color.
The line of the selected State will have a blue shading and the rest of the States a light gray color.
For its definition we will use the “Format Shading” menu.
- Light blue will be used for shading in the “Worksheet”.
- For shading of “Row Banding” the light gray will be used and the “Band Size” and the Level will be adjusted as indicated in the following image. These adjustments will allow us that the entire block of the States that are OUT in the Set will have a gray shading.
Recall that when creating the Set in step 1, we select a State: “Pennsylvania”, which should appear as the first item in the list and with a blue shading.
Step 5: Create a Set Actions
Recall that the “Set Actions” take an existing “Set”, in this case “State Set” and update the values contained in that “Set” based on the action of a user in the visualization.
To create the Set Actions, I selected the option from the “Worksheet” menu and used the following definition:
Test the functionality of the Set Actions selected other states.
STEPS FOR THE DEFINITION OF LISTINGS BY SEGMENT
For each of the Segments, the State selected in the main chart and four other similar States within the segment must be listed. But what should we consider as similar states?
- Similar Ranked: They are the two States ranked directly above and below the selected State (or the four closest States in the “Rank”, in case the selected State is close to the Top or the Bottom and there are no two States above below).
- Similar Sales: They are defined as the four States that in sales $ are closer to the sales $ of the selected State
As both options may be valid, I will let the user select the type of proximity.
Step 6: Define parameter to select the type of proximity.
Create parameter with name “Parameter” of Boolean type:
True = “By Similar Ranked”
False = “By Similar Sales $”
Step 7: Define the value for the Selected State according to the option chosen by the user in the parameter (Rank or Sales).
We will use the Window_Max function to obtain the maximum value considering only the values of the selected State.
|Value of Selected State =
WINDOW_MAX(IIF(ATTR([State Set]), [Rank], NULL)),
WINDOW_MAX(IIF(ATTR([State Set]), SUM([Sales]), NULL)))
Step 8: Define the value for each of the States according to the option chosen by the user in the parameter (Rank or Sales).
In accordance with the value of the parameter, the value of the Rank or the value of the Sales in each of the States will be taken.
|Value by State =
IIF([Parameter], [Rank], SUM([Sales]))
Step 9: Define the filter to select the 5 States that should be deployed.
This will help us to select only the 5 States that will be shown in the list of each Segment.
It’s required to calculate a new “Rank Unique” of “ascending” order on the difference in absolute values, between the Value of the selected State minus the Value of each State.
Finally, the condition that the single Rank has a lower value equal to 5 must be met, since we only want to show 5 States in each list.
RANK_UNIQUE(ABS([Value of Selected State] – [Value by State]), “asc”) <= 5
Below, I share two examples of the values involved in the calculation of the “Rank Unique” according to the selection of the State user and the value of the Proximity Type Parameter.
In some cases, the states selected when applying similarity in the Rank may coincide with those selected when applying the similarity for Sales, as in the first example. But on other occasions they give different results, as in the second example.
Step 10: Create chart for sales of the “Consumer” segment.
Move to the shelves of:
- Filter ⬅ “Segment” = “Consumer”
- Columns ⬅ SUM(Sales)
- Rows ⬅ “Rank” y “State”
- Color ⬅ “State Set”. Moving it will become “IN / OUT (State Set)”
- Text ⬅ “SUM(Sales)”
- Filter ⬅ “Filter” = TRUE
Each of the nested Table Calculations of “Filter” must be calculated using the specific dimensions of “State” and “In / Out State Set”.
The adjustment must be made in each of the nested calculations: “Filter”, “Value of Selected State” and “Rank”.
The calculation of “Rank” Table in the row shelf, will also adjust the definition to calculations by specific dimensions of “State” and “In / Out State Set”.
Step 11: Copy the chart for sales of the “Consumer” segment and change the filter for “Corporate”.
Step 12: Copy the chart for sales of the “Consumer” segment and change the filter for “Home Office”.
One of the main challenges this week was to decide which States are close or similar, regardless of whether I should look for them above or below the selected State or if the selected State is close to the Top or Bottom of the list.
The key to the solution was the use of the “Rank_Unique” function, which, remember, for identical values assign different and unique Ranks as the name implies.
But what is the value that we should use within the “Rank_Unique” function?
The difference in absolute values (that is, always positive values) between the value of the Selected State vs. the value of each State.
Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag)