Heat Map

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.

16

Tutorial

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”.

HM11

2.- Create a dummy expression:

HM8

3.- Expand and drag the second dimension in order to create a Cross Table.

HM5

4.- Adjust the column size, captions and alignment.

HM4

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:

ColorMix1

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?

Today’s sales:

HM7

The day with the biggest sales of the whole year =

HM6

Use these formulas and add colomrmix1(). I decided to use a RGB to archive a white-blue gradient:

HM9

Paste it in the “Background color” section of your expression:

HM10

So far your chart sould look like this:

HM12

6.- In order to “hide” those Xs we can apply the same color to the font:

HM13

HM14

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:

HM15

Also, add this formula to the “Text format” section of the expression to use bold letters.

HM19

Now, when you put the cursor over a cell it should look like this:

HM16

(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:

HM17

HM18

And that’s it!

Please leave a comment if you have any suggestions or doubts.

Until the next post Qlik Freaks!

Advertisements

26 thoughts on “Heat Map

  1. Edecio says:

    Hi Julian! I’m here again because i have a problem with that heat map. Can you help me out? When i’m using the aggr() function, the QV is not using as suppose to. He is aggregating by the hightest day. PS.: My code it’s just like yours.

    • Hi Edecio, sure! I need to learn a little more about your app:
      – What are your dimensions?
      – Use =1 as your dimension
      – Create a text object with this formula: $(=max(aggr(sum(Sales), OrderDate)))
      What’s the result?

  2. Galina says:

    Hi Julian!

    Thank you for your post. Could you please add a simple example of app for this post. I try to implement it in my own app and the result of the expression sum / min (aggr (sum ()) … is always 1.
    When I calculate aggr in separate text object my result is correct, but when I add it into pivot table I have wrong result.

    Thank you for you comments.

  3. Galina says:

    Hi Julian!
    Sorry for spaming you today :).
    I found your app at the begging of your article. Thanks a lot. So now I can not understand why the expressions
    max(aggr(sum(Sales), OrderDate))
    and
    =$(=max(aggr(sum(Sales), OrderDate)))
    have different results in our pivot table.

    Thank you in advance for your comments

    • Hi Galina,
      Well, those expressions have different results because the first one is calculated “on each cell” and the second one is “pre-calculated”. Let’s say that when you use the syntax $(=), the expression is calculated “outside” the table and then, the result is inserted in all the cells. When you use the normal expression, QlikView calculates it for each value in the dimension as a mini-universe.
      Hope it helps!

    • Hi Mark,

      Visually, you only need two text objects for the max and min numbers respectively. In the middle, add a third text object using a gray border and a Two Colors Gradient from blue to white.

      In order to calculate the max and min numbers, you can use an AGGR function that checks the Sales figure for every date:

      =money(max(aggr(sum(Sales), OrderDate)))

      Hope it helps!

  4. Donnie Clark says:

    This is fantastic. On the popup text colors, I was able to get them to show up in black (much more visible) by changing the representation of the column from text to image.

    You still have to put your formula in the text color option, otherwise the black font shows up in the cell, but the popup font will be black. Not sure why it works, but it does for me in 11.2 SR 9.

  5. Kevin says:

    Great post! Maybe an extra tip:
    Instead of the =’X’ expression you could also use chr(10) as the expression. This way you don’t have to hide the X’s and the pop-up text will always be clearly visable.

    • The legend is just a simple text object with a gradient color as background. The Min and Max values are calculated in another text object using this formula:

      =money(max(aggr(sum(Sales), OrderDate)))

      Hope it helps!

  6. Cesar Roldan says:

    When i select any object HeatMap show cells with values. What can i do for keep show the blue-white when other object is selectd???

    • You can add a set analysis clause that disregard certain selections like “Product=”. What kind of selection are you applying?

  7. Fantastic!! One small issue, the text pop up to display the values work perfect in the developer, but do not appear in WebAccess. Has anyone else experienced this? Any solutions?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s