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.

ezgif.com-optimizeBelow 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”.

Picture02

 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

Picture04

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.

Picture03

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