For week 29 of #WOW, Ivett Kovács and Tamás Gáspár prepared a challenge for us and in the advanced version asks us to build a Heatmap, where the color and the display of the labels is determined, according to whether the user hovers a cell or if the user selects a cell.
In the final design of my exercise, I included some additional requirements that I found interesting while building the Viz.
Requirements:
Initial state of the Viz:
- Heatmap by order year and order month, which must include the row and the totals column.
- The color of the map will be defined by the average of the quantities sold.
- When starting, only the average sales labels will be visible in the total cell.
When the user hovers over a cell:
- The label of the selected cell should be visible.
- In case the cell you are moving over is a total, the label should be shown in all the cells of the row or column as appropriate.
When the user selects a cell:
- The color of the map will now be defined by the difference between the average of the quantities of each cell vs. the value of the selected cell.
- The color for the total cells must also be adjusted based on the definition in the previous point.
- All the cells in the row and column that correspond to the selected cell should be highlighted.
- The highlight should also apply for when the selected cell is a total.
- Show the label in each of the cells, both the average of the quantities sold, and the variation vs. the selected cell.
Below I share the procedure I followed.
STEP 1. CREATE CUSTOM DATES OF YEAR AND MONTH.
Select the Order Date field and with the right-hand click activate menu and select “Create> Custom date”.
STEP 2. CREATE SETS.
Two sets of Set will be defined for date and for month. Initially the Sets will not have selected values:
For the option: Hovers over a cell.
| Hover Months | Set created from the Month dimension |
| Hover Years | Set created from the Year dimension |
For the option: Select a cell.
| Select Months | Set created from the Month dimension |
| Select Years | Set created from the Year dimension |
STEP 3. CALCULATE THE FOLLOWING DIMENSIONS.
For each one of the cells of the heat map, it’s required to determine if the cell is selected or not by the user and if it’s a totals cell. Also determine if the selected cell is a totals cell.
| This Cell is Selected? | [Select Years] AND [Select Months] |
| This Cell is Total? | COUNTD([Year]) <> 1 OR COUNTD([Month]) <> 1 |
| Selected Cell is Total? | { COUNTD(IIF([Select Months], [Month], NULL)) } > 1 OR
{ COUNTD(IIF([Select Years], [Year], NULL)) } > 1 |
STEP 4. CALCULATE THE VALUE OF THE SELECTED CELL.
An LOD calculation will be used to determine the average value of the sales of the selected cell, if a cell is not selected, its value will be null.
| Qty of Selected Cell | { AVG(IIF([This Cell is Selected?], [Quantity], NULL)) } |
STEP 5. DEFINE THE VARIABLE FOR THE COLOR FIELD.
The color is determined by whether a cell is selected or not.
- In the absence of a selected cell, the color will be determined by the average sales.
- If there is a selected cell, the color will be determined by the difference between the average value of the quantities sold in each cell vs. the value of the selected cell.
| Color | AVG([Quantity]) –
IIF(ISNULL(AVG([Qty of Selected Cell])), 0, AVG([Qty of Selected Cell])) |
STEP 6. DEFINE HIGHLIGHT..
The calculation helps us to identify the cells that will be highlighted when a cell is selected. In such a way that the selected cell will be the crossing cell between the row and the column to be highlighted.
Its calculation is determined by whether the selected cell is a total of years, or is a total of months, or is an individual month-year crossover cell
| Highlight | IIF(ATTR([Selected Cell is Total?]),
((COUNTD([Year]) <> 1 AND ATTR([This Cell is Selected?])) OR ATTR([Select Months]) OR COUNTD([Year]) <> 1 ) AND ((COUNTD([Month]) <> 1 AND ATTR([This Cell is Selected?])) OR ATTR([Select Years]) OR COUNTD([Month]) <> 1 ), ATTR([Select Years]) OR ATTR([Select Months])) |
STEP 7. DEFINE CONFIGURABLE LABELS.
Configurable labels are presented that will depend on whether the user is moving over a cell or has selected a cell. In addition, the label of the quantity value will be divided into two calculations, to use different fonts depending on whether the cell is selected or not.
- The average value of the quantities for the selected cell.
- The average value of the quantities for the unselected cells.
- The value of the variations vs. the selected cell.
| Label Qty of Cell Selected | IIF(ISNULL(AVG([Qty of Selected Cell])), NULL,
IIF([This Cell is Selected?], AVG([Quantity]), 0)) |
| Label Qty of Cell Not Selected | IIF(ISNULL(AVG([Qty of Selected Cell])),
IIF([This Cell is Total?] OR (ATTR([Hover Years]) AND ATTR([Hover Months])), AVG([Quantity]), NULL), IIF([This Cell is Selected?], NULL, AVG([Quantity]))) |
| Label Variation | IIF(ISNULL(AVG([Qty of Selected Cell])), NULL, [Color]) |
STEP 8. CREATE THE MAIN VIEW.
Move to the shelves of:
- Columns ⬅ Month
- Rows ⬅ Year
- Color ⬅ Color
- Detail ⬅ Highlight
- Label ⬅ Label Qty of Cell Selected
- Label ⬅ Label Qty of Cell Not Selected
- Label ⬅ Label Variation
STEP 9. BUILD THE DASHBAORD
Include the view created in a Dashboard and format.
STEP 10. ADD DASHBAORD ACTIONS
We will define two Set Actions for when the user moves between the cells.
| SET ACTION | TARGET SHEETS | RUN ACTION ON | TARGET SET | RUNNING THE ACTIONS WILL | CLEARIN THE SELECITION WILL |
| Hover Months | Chart | Hover | Hover Months | Assign Value to Set | Remove all values of set |
| Hover Years | Chart | Hover | Hover Years | Assign Value to Set | Remove all values of set |
We will define two Set Actions for when the user selects one of the cells.
| SET ACTION | TARGET SHEETS | RUN ACTION ON | TARGET SET | RUNNING THE ACTIONS WILL | CLEARIN THE SELECITION WILL |
| Select Months | Chart | Select | Select Months | Assign Value to Set | Remove all values of set |
| Select Years | Chart | Select | Select Years | Assign Value to Set | Remove all values of set |
We will define a Highlight Action.
Ready!
The interactive version can be found here.
I thank Ivett Kovács and Tamás Gáspár for sharing such an interesting challenge with us.
I hope you enjoyed reading and let me know if you have any questions.
Rosario
