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, the How to create custom metric video 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
Comments
8 comments
I found that the values needed to be .0# rather then 0.0#.
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?
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!
All good, William! Thanks for updating 😄
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?
@ 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, 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 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.