Create a Heat Map Table

This tutorial provides information on how to create a ticket creation heat map, although it can be easily adapted for use with a count of occurrences and the associated timestamp of the event.

In the image below, you can see a ticket creation heat map, in which the times when support tickets are created are tracked throughout the week, which can assist in addressing staffing needs and scheduling:

   The above report uses green as the primary color. At the end of this tutorial are cut-and-paste options for configuring your report to be green, red or blue.

Prerequisites

Before you begin, you should verify that the hour of the event that you are tracking is available in your dataset as an attribute, and that it is connected to the metric you are counting. This information is not available by default using the provided Date dimension.

Steps:

These steps below describe how to create the Ticket Creation heat map report shown above, tracking the heat of the # Tickets metric throughout the work week.

  1. Create a new report:

    • In the Custom Metric Editor, create the following metric for the percentage of tickets compared to the overall count of tickets. The Ticket Percentage metric:

      SELECT # Tickets / (SELECT # Tickets BY ALL OTHER)

    • In the How pane, select the Day of Week (Mon-Sun) attribute.

    • In the How pane, select the select the Hour attribute that you have added to your dataset.

    • In the Filter pane, click Select from a List of Values (including date ranges). Select the Week (Mon-Sun) / Year (Created) attribute. Set the filter to include values for as far back as you are interested in tracking their heat.

  2. This percentage can now be compared against other values to show a relative scale. You should now see the values in the table, broken out by hour of the week. To make the table more readable, you can apply conditional number formatting to instruct the GoodData Portal to format the boxes based on specified color values. These color values should be relative to the other boxes in the table, so that you can measure the “heat” of each time period against the other periods in the table. For clarity, you can also hide the values, so that the users focus on the colors only.

  3. Here, we use custom formatting codes to generate colors. The colors are defined by the Custom Formatting module in the Show Configuration panel. For more information, see Formatting Numbers in Insights and Reports

 
  1. Below are the conditional number formatting for three different color schemes. You can choose the one that works for you. Just copy and paste the code you want in the custom formatting box and modify as necessary.

    BlueRedGreen

    [>=.011][backgroundcolor=0A1F33][color=0A1F33]#%;

    [>=.01][backgroundcolor=143D66][color=143D66]#%;

    [>=.009][backgroundcolor=1F5C99][color=1F5C99]#%;

    [>=.008][backgroundcolor=297ACC][color=297ACC]#%;

    [>=.007][backgroundcolor=3399FF][color=3399FF]#%;

    [>=.006][backgroundcolor=5CADFF][color=5CADFF]#%;

    [>=.005][backgroundcolor=85C2FF][color=85C2FF]#%;

    [>=.004][backgroundcolor=ADD6FF][color=ADD6FF]#%;

    [>=.003][backgroundcolor=D6EBFF][color=D6EBFF]#%;

    [>=.002][backgroundcolor=EBF5FF][color=EBF5FF]#%;

    [<.002][backgroundcolor=ffffff][white]0;

    [=Null][backgroundcolor=ffffff][white]0

    [>=.011][backgroundcolor=660011][color=660011]#%;

    [>=.01][backgroundcolor=99001A][color=99001A]#%;

    [>=.009][backgroundcolor=CC0022][color=CC0022]#%;

    [>=.008][backgroundcolor=FF002B][color=FF002B]#%;

    [>=.007][backgroundcolor=FF3355][color=FF3355]#%;

    [>=.006][backgroundcolor=FF667F][color=FF667F]#%;

    [>=.005][backgroundcolor=FF99AA][color=FF99AA]#%;

    [>=.004][backgroundcolor=FFCCD5][color=FFCCD5]#%;

    [>=.003][backgroundcolor=F7D4DA][color=F7D4DA]#%;

    [>=.002][backgroundcolor=F0DBDF][color=F0DBDF]#%;

    [<.002][backgroundcolor=ffffff][white]0;

    [=Null][backgroundcolor=ffffff][white]0

    [>=.011][backgroundcolor=226600][color=226600]#%;

    [>=.01][backgroundcolor=339900][color=339900]#%;

    [>=.009][backgroundcolor=44CC00][color=44CC00]#%;

    [>=.008][backgroundcolor=55FF00][color=55FF00]#%;

    [>=.007][backgroundcolor=77FF33][color=77FF33]#%;

    [>=.006][backgroundcolor=99FF66][color=99FF66]#%;

    [>=.005][backgroundcolor=BBFF99][color=BBFF99]#%;

    [>=.004][backgroundcolor=DDFFCC][color=DDFFCC]#%;

    [>=.003][backgroundcolor=DFFAD1][color=DFFAD1]#%;

    [>=.002][backgroundcolor=E3EDDE][color=E3EDDE]#%;

    [<.002][backgroundcolor=ffffff][white]0;

    [=Null][backgroundcolor=ffffff][white]0

VIDEO - How to Create Heatmaps