By Ray Light, GoodData
This report shows how quickly tickets are created during the day. This is useful for identifying peak hours and determining whether your team is adequately staffed.
Creating your report
You only need one metric to create this report. This metric must be created in the Advanced Metric Editor. For more information on the Advanced Metric Editor or creating custom metrics see Creating metrics and reports on GoodData's support site.
The metric is defined as:
- SELECT # Tickets computes the number of tickets. This metric is sliced by attributes and can be selected through the pick-list Metrics folder.
- (SELECT # Tickets BY ALL OTHER) divides the number of tickets in each attribute by all eligible tickets. The BY ALL OTHER clause locks the aggregation to include all # Tickets .
This report uses the Day of Week (Sun-Sat) Ticket Created and Created at Hour attributes. These attributes distribute tickets by the day and hour created.
Generating the heatmap
Once you create your report, you can now set the primary color of your heatmap. This report uses colors to compare values to other values, instead of the actual result. To configure these colors, you can use custom formatting codes in the Custom Formatting module. At the end of this report are blue, green, and red formatting codes. You can copy-and-paste these codes directly.
- Blue:
[>=.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
- Red:
[>=.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
- Green:
[>=.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
13 Comments
Hi, I added Sum and Average on the column. The color formatting is also applied to those...
Hi Riah!
I'm not sure what you're asking. Is what you're seeing not what you expected?
Hi Jessie, because if I put in values for the range, the Sum will always be meeting the max value and it just looks off.
This is a very useful heatmap, however I have received requests to also show the number of tickets that were created for each hour as well. I can add # Tickets to the What portion of the report, however that puts the ticket numbers in a separate column, thereby making the heatmap much harder to digest. Is there a way to have the colored heatmap with the number of tickets showing up in the colors themselves?
Hi, I am trying to make this report, however the color portion is not working. No matter what the number is, it is the same color. Do you have any suggestions? Thank you!
Hi Jesse, I expect the color formatting to be only applied to the main columns and not the Sum/Average columns
For the How attributes, I have the Day of Week (Mon-Sun) (Ticket Created) but I do not see any attribute for Created at Hour. Is that a custom attribute?
*Edit:
I think it might actually be called Hour Ticket Created instead of Created at Hour?
I found that the values needed to be .0# rather then 0.0#.
All good, William! Thanks for updating 😄
Thanks for clarifying, Riah!
I believe what's happening here is that the heat map report format isn't built to to automatically exclude any additional columns from being colored. The assumption is that if you're adding a column to the heat map you want to see how those values compare to the other values in the report.
Can you tell me more about why you want those values included but not colored in? There might be another solution.
@ Justin,
If you don't want to add additional metrics to the report you can make the percentage visible, now it is hidden because the font color is set to the same color as the background. You can update this in the number format editor.
For example, if you want to set the font as white use this color coding:
[>=.011][backgroundcolor=0A1F33][color=000000]#%;
[>=.01][backgroundcolor=143D66][color=000000]#%;
[>=.009][backgroundcolor=1F5C99][color=000000]#%;
[>=.008][backgroundcolor=297ACC][color=000000]#%;
[>=.007][backgroundcolor=3399FF][color=000000]#%;
[>=.006][backgroundcolor=5CADFF][color=000000]#%;
[>=.005][backgroundcolor=85C2FF][color=000000]#%;
[>=.004][backgroundcolor=ADD6FF][color=000000]#%;
[>=.003][backgroundcolor=D6EBFF][color=000000]#%;
[>=.002][backgroundcolor=EBF5FF][color=000000]#%;
[<.002][backgroundcolor=ffffff][white]0;
[=Null][backgroundcolor=ffffff][white]0
Screenshots:
The repot will look like this:
However, there is no way to display a number of tickets instead of the percentage via the same metric.
Hi Erin,
Sorry for the delay-- you are correct that Hour Ticket Created would be the correct replacement for the Created at Hour attribute listed currently. I'll make a note about updating the article. Thanks for pointing that out!
Hi Miriam! Are you using both Day of Week (Sun-Sat) (Ticket Created) and Hour Ticket Created for your attributes? If you've followed the article instructions and are still not seeing what you expect, send us a ticket directly with a link to your specific report so we can check out your account and have a look. :)
Please sign in to leave a comment.