Ivett Kovacs returned with the #WOW challenge for this week, providing 2 versions. She provided some hints in the requirements which helped. With a bit of head scratching I managed to deliver an advanced version too. It didn’t exactly match what Ivett had delivered, but I was quite happy with my version. Until my follow #WOW participant Rosario Guana pointed out that the display ‘on click’ of a row or column total didn’t work properly 😦
I’ve spent A LOT of time trying to figure out what’s required but can’t resolve it. I’ve checked out Ivett’s solution and noticed a slight flaw in her logic too, so I feel uncomfortable changing my flaw for another. I’ve also looked at other solutions, and all are showing slightly different behaviour when you click on a column or row cell total. As a result, I’ve decided to blog based on the Intermediate version only. Others have posted blogs about their Advanced versions, so I’m going to save some time today, and reference them instead.
Building the basic heat map
The core of this challenge is going to involve Sets, and we’re going to use Set Actions to identify which cell is being interacted with.
For the intermediate version, we’re interacting based on hover actions.
The heatmap displays a grid of months by years, and we need to build sets based off of these. So we’ll start by creating dedicated calculated fields storing these values
Month Order Date
Year Order Date
Add Month Order Date to Columns and Year Order Date to Rows, then drag Quantity onto Colour and change to AVG. You should have your basic heatmap.
From the Analysis -> Totals menu, select to shoe row & column grand totals. They will initially display as white cells, so you need to Edit Colours on the colour legend and select Include Totals/
Let’s sort some formatting out at this point.
Set the mark type to Square
Format the borders by setting the row & column dividers to none, but setting the Grand Total pane & header to be thick, white lines
And change the word Grand Total, by right clicking -> format on each cell and changing the Grand Total label.
Adjust the size of the Month & Year labels and remove header labels from displaying and you have
Labelling the total cells
We need to permanently display the label on the total row/column, and only show the label on the cells ‘on hover’.
We need a way to identify when we’re on a total cell. Ivett gave a hint “What is the Size() of the totals and the cells?“. This is a trick I’ve read about and discussed here. Basically the size() of the grand total row or column will be 1, so this is what we’re going to base some logic on.
Size of Months
Size of Years
IF [Size of Years]= 1 OR [Size of Months] = 1 THEN AVG([Quantity]) END
If we’re on a row or column grand total, then show the AVG(Quantity). Add this onto the Label shelf, and edit the table calculation so the Size of Years nested calc is computing by Year Order Date, and the Size of Months nested calc is computing by Month Order Date.
Labelling the central cells
We need to create some sets, as these are going to determine whether we are hovering on a cell or not. The dashboard action will add values to a set on hover.
Right click on Month Order Date and Create Set, selecting an arbitrary month.
Hover Month Set
Do the same with Year Order Date
Hover Year Set
We then need a field to label the cell
IF ATTR([Hover Month Set]) AND ATTR([Hover Year Set]) AND [Size of Months]>1 AND [Size of Years]>1 THEN AVG([Quantity])
If something has been selected in the Hover Month Set and something has been selected in the Hover Year Set, and we’re not on a grand total row/column, then show the AVG(Quantity).
Add this to the Label shelf, and edit the table calculation settings as above.
The tooltip displays the Profit Ratio
so you’ll need to create this field, add to the tooltip and adjust the display accordingly.
Add the interactivity
Add the above sheet onto a dashboard, then create a dashboard action to Change Set Values, which is set to
- run on Hover
- Target the Hover Month Set
- Assign values to the set when run and remove values from the set when cleared
Repeat to create a set action to target the Hover Year Set.
Test it out – hover your mouse over the cells. If you hover on a total, you should get the values displayed for all the values in the row or column
As I stated in the intro, I didn’t end up with something I was entirely happy with. While writing this up, I’ve tried to rebuild using ideas from other published solutions, but I still have got anything ‘perfect’.
Ivett’s solution is obviously the closest to what we’re aiming for, since that is the challenge. The minor flaw I found in that though was that in some of the labelling logic there’s a step which compares the ‘average of the selected cell’ with the ‘average of the current cell’, and if they match, display the AVG(Quantity), otherwise show the difference. This logic is basically trying to work out ‘if current cell = selected cell’, but it doesn’t fully work, as there are other cells where the values match – it’s subtle as the displayed values are faded out with the cell highlighting, as I’ve shown in the example below.
Rosario Guana has blogged her solution here. Rosario has adapted her solution slightly and displays both the average values and the difference ‘on select’, so the logic I was struggling with, isn’t being used in the same way here.
No doubt with a bit more time, I might finally crack this one, but for now, I need to ‘put it to bed’ 🙂
Happy vizzin’! Stay safe!