Last week I found an interesting post about the fatal crashes in the US. It used a fascinating way of representing data and I thought it would be a good chart to complement some of my applications. The idea was simple: create a heat map based on a classic calendar.
First of all, you can find a QVW file with this chart here. You can save it in your QlikView Resource Library so you can easily paste it whenever you need it. I used a simple sales application to create this example. The steps are:
1.- Create a pivot table with 2 time dimensions. I found two options that work really well with this analysis, “Week and WeekDay” or “Month and Day”.
2.- Create a dummy expression:
3.- Expand and drag the second dimension in order to create a Cross Table.
4.- Adjust the column size, captions and alignment.
5.- In order to give color to each cell we will use the function colormix1. This function creates a gradient between two colors using a number that goes from 0 to 1. For example:
You can use the standard color functions like red(), blue() or black(). If you need something specific you can use custom colors like RGB(0, 70, 140).
The only problem here is how to get the number between 0 and 1 to calculate the gradient. We know that the most intense cell should be the day with the most sales, accidents or car crashes (depending on your metric), so we can assume that it’s safe to do something like this:
Today’s sales / The day with the biggest sales of the whole year
$80,000 / $120,000 = 0.66
Today’s gradient should be 0.66.
Now, how to implement this logic in QlikView?
The day with the biggest sales of the whole year =
Use these formulas and add colomrmix1(). I decided to use a RGB to archive a white-blue gradient:
Paste it in the “Background color” section of your expression:
So far your chart sould look like this:
6.- In order to “hide” those Xs we can apply the same color to the font:
7.- And a final detail, the pop ups. We will change the =’X’ expression to something more meaningful. Erase the current content of the expression and use:
Also, add this formula to the “Text format” section of the expression to use bold letters.
Now, when you put the cursor over a cell it should look like this:
(I know the day with low sales are nearly invisible, but hey, it’s not that bad…)
8.- Your final result by week or month should look like this:
And that’s it!
Please leave a comment if you have any suggestions or doubts.
Until the next post Qlik Freaks!