Challenge, Workout Wednesday, Trellis Chart, Small Multiple Chart, Area
By: Rosario Gauna @rosariogaunag
August, the month of the challenges of the Workout Wednesday community, says goodbye with an excellent exercise prepared by Zen Master Neil Richards. Link to the challenge
The training corresponds to week 35 of Workout Wednesday consists in recreating a “small multiple chart” version of a 2 × 2 quadrant graphic.
A multiple chart or trellis chart is a series of similar charts or tables that use the same scale and the same axes to show different partitions of a data set, allowing you to easily compare and contrast values between the charts.
Next, I share the final procedure that I used:
Step 1: Create a new dimension based on the values of the Category and Sub-Category fields.
The objective of the creation of this field is to maintain the synchronicity in the visualization of this chart, since it is required to work with 4 different values within the dimension to be graphed. For Superstore data, the Category field manages three values: Technology, Furniture and Office Supplies. To have a fourth value, the Technology category should be divided into two values: Telephones and Other Tech. In this way, the new dimension will manage 4 values: 1) Telephony, 2) Other Tech, 3) Furniture and 4) Office Material.
Category Chart =
IIF( [Category] = “Technology”, IIF( [Sub-Category] = “Phones”, “Phones”, “Other Tech”), [Category]) |
Step 2: Calculate the Profit Ratio by State and Category and its maximum value per State.
Profit by State-Category =
{ FIXED [State], [Category Chart] : ROUND( SUM([Profit]) / SUM([Sales]), 4 ) } |
Max Profit by State =
{ FIXED [State] : MAX( [Profit by State-Category] ) } |
Step 3: Calculate for each State, its position or coordinates. That is, in which column and row should appear.
The Superstore data set has 49 states included (not Alaska or Hawaii, but the District of Columbia is included), which gives us a perfect data set for a 7 × 7 grid. Because the size of the grid will not change, you can use a short version of the calculation of the coordinates, leaving fixed the value of 7 states plotted by row.
COLUMN =
INT( (Index() – 1) % 7) |
ROW =
INT( (Index() – 1) / 7) |
Right click on both fields and select the “Convert to Discret” option
Step 4: Calculate the height and width of each square of each of the Categories within the grid of the 2 x 2 internal chart.
Given that the area of the square will represent the Profit Ratio, to obtain the size of the sides it is required to obtain the square root of the Profit Ratio.
A second point to observe in the formula is to ensure that the negative Profit Percentages are excluded from the graph.
As a last point, the quadrant corresponding to each Category should be considered. Since the quadrant will define the sign to use according to the following table:
X =
IIF( [Profit by State-Category] > 0, IIF( [Category Chart] = “Other Tech” OR [Category Chart] = “Office Supplies”, 1, -1) * SQRT( [Profit by State-Category] ), NULL) |
Y =
IIF( [Profit by State-Category] > 0, IIF( [Category]=”Technology”, 1, -1 ) * SQRT( [Profit by State-Category] ), NULL) |
Step 5: Calculate the “Color” field and the “Label” field for the quadrant with the highest Profit Ratio by state.
Color =
IIF( [Profit by State-Category]= [Max Profit by State], [Region], “Gray”) |
Label =
IIF( [Profit by State-Category]= [Max Profit by State], [Max Profit by State], NULL) |
Step 6: Create a “Bar” chart and a “Text” chart
For the bar chart you must change the option “Stack Marks” to “Off”. If we did not modify this option, the value of the bars of each one of the categories would be accumulated according to the order in which they are displayed.
The “Text” graphic will be used to place the name of each State in the chart when using a synchronized double axis.
Move to the shelves, the variables indicated in the following image:
For the axes defined with the variables AVG(0) and AVG(Y) establish a fixed range with values between -1 and 1. For the axis defined by the variable AVG(-0.8) that it will be a synchronized double axis, which It will be used to include the name of each State.
For the “Column” and “Row” variables that use the Index () function within their formula, it must be established that the table calculation is by specific dimensions. It is very important not to forget that the calculation must be done at the State level to ensure that the value of the index increases when the State changes, regardless of whether the State contains data or not for all the Categories.
To finish shaping the chart, we use a trick that will help each of the bars of the chart coincide so much in height and width, that is, draw square figures.
To achieve this goal, we will use the “Fixed” option of the “Size” shelf, with which the width of each of the bars will be controlled according to the value set in the variable AVG(X) and indicate that requires a left alignment for the bars.
Step 7: Adjust the tooltips and the formats.
Ready!
I would like to take this opportunity to thank Neil Richards for sharing this fun challenge, with which we will finish off the month of the challenges of the Workout Wednesday community with a flourish.
If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)
Regards!
Rosario Gauna