This Explore recipe shows you how to change the color of the% One-touch tickets价值green when it reaches a certain value.
What you'll need
Skill level:Average
Time Required:15 mins
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (seeGiving agents access to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
Use the following steps to create this report in Explore.
- In Explore, click the reports (
) icon. - In the Reports library, clickNew report.
- On theSelecta datasetpage, clickSupport>Support -Tickets, then clickStart report. The report builder opens.
- Next, add your metrics, the things you want to measure. In this case, you'll add the number of tickets created. In theMetricspanel, clickAdd.
- From the list of metrics, chooseAgent replies distribution> %One-touch tickets, then clickApply.
- IntheRowspanel, clickAdd.
- From the list of attributes, chooseTime - Ticket created>Ticket created - YearandTime - Ticket created>Ticket created - Month. Then, clickApply.
- In the chart configuration menu (
), clickDisplay format. - On theDisplay formatpage, changeStandardtoAdvanced.
- Copy and paste the code below into theFormatsection.
IF (SUM(onetouchtickets-c133534e9b) > .80) THEN
{
"backgroundColor": "#15B660",
"precision": 0,
"scale": .01,
"prefix": "",
"decimalSeparator": ".",
"fontColor": "",
"italic": FALSE,
"bold": FALSE,
"suffix": "%",
"thousandsSeparator": " "
}
ELSE
{
"backgroundColor": "#ff0000",
"precision": 0,
"scale": .01,
"prefix": "",
"decimalSeparator": ".",
"fontColor": "",
"italic": FALSE,
"bold": FALSE,
"suffix": "%",
"thousandsSeparator": " "
}
ENDIFYou can change the required reference value. In the example, it shows.80. You can also change the color that appears when the value is greater than the reference value. In the example, the HEX color value is #15B660. Finally, you can also change the color that appears when the value is less than the reference value. In the example, the HEX color value is #ff0000. For more information, seeColor Hex.
The report is now complete.
24 Comments
Hi,
Does the code for formatting support extended IF statements like this?
Hi Andrei,
The IF block does support multiple conditions on both the metrics and the attributes. That said, we recommend testing out each condition individually first before conjoining them in a single condition (using AND/OR).
Warm Regards,
Eric
Eric G. Gao | Technical Support Architect | Zendesk
I was unable to get the color formatting working with KPI visualization type. I switched to a Table visualization and it worked. Just FYI in case anyone was having issue.
Edit: Link to the feature request.https://support.zendesk.com/hc/en-us/community/posts/360042104174-Explore-color-conditional-formatting-is-only-in-Table-format-
How to change color of Priority and Ticket solved month, in the given query, please refer snapshot of the query.
Unlike for example if you have the Ticket Solved - Month as a column and the Priority as a row, which would result into something like this automatically changing colors. In here, we are telling Explore to display the number of tickets being created (or solved in your case, just used created here as it has more data in my test account), and and separate them by priority.
Hope this helps! Cheers!
Hey, how do I write > or =? Because here we get bigger or smaller and if the value is equal will be included as smaller. What is the simbol?
You can use OR, e.g.:
IF (SUM(onetouchtickets-c133534e9b) > .80) OR (SUM(onetouchtickets-c133534e9b) = .80) THEN
See alsoExplore functions reference
Hi Dave,
Thanks for answering.
I tried but it didn't work to me. So i decided just put a number behind, like the last number i want to have red. i.e. .69 when your target is 70. That works too.
Have a great day
Hi,
Do you know any way of excluding Totals from the formatting?
Totals are considered as part of the metric values and the conditional formatting will always be applied.
The only alternatives are to remove the totals from the query or include additional logic in the conditional formatting to remove the color when the totals exceed a certain value.
Hope this helps.
Cheers,
Dane
Hello,
I would like to know if it can be possible to define different colors on graphs got with explosions (see example below):
I would like to have a different graph color for each group in my example.
Thanks a lot.
Olivier
HiOlivier Saugoux, you can use theSWITCH functionto accomplish this. What you’ll need to do is create and add astandard calculated metric(in addition to the metric you already have in the screenshot above) with a formula that follows this pattern:
SWITCH ([Group]) {CASE "Name of group 1": 1
CASE "Name of group 2": 2
CASE "Name of group 3": 3
CASE "Name of group 4": 4
DEFAULT: 5
}
Make sure to use the AVG aggregator with your standard calculated metric. After you’ve added this new metric to your report, click it and selectColor(near the bottom) and clickApply.
Your explosions should now be showing in different colors. You can modify the colors a bit by going to theChart configurationmenu >Colors>Color encoding. Note that the colors will be determined on a spectrum—you won’t be able to pick a specific color for a specific attribute value.
Here's an example of what my test report looks like using this method:
Hello! If I were looking to use this with the median resolution time metric ( MED(Full resolution time(hrs)) ) how would you plug that in? We're looking for anything at or below 2.5 hrs to show up in green and anything at 2.6 hrs or higher to be red. For some reason, I can't get the code to cooperate and it's throwing errors before I've changed anything.
The existing code:
IF (MED(Full resolution time (hrs)) > 50) THEN
{
"backgroundColor": "",
"precision": 0,
"scale": 1,
"prefix": "",
"decimalSeparator": ".",
"italic": FALSE,
"bold": FALSE,
"suffix": "",
"fontColor": "",
"thousandsSeparator": " "
}
ELIF (IS_NAN(MED(Full resolution time (hrs)))) THEN
{
"backgroundColor": "#dddddd"
}
ELSE
{
"fontColor": "#30aabc"
}
ENDIF
Any advice would be greatly appreciated!
Let me create a ticket for you so that we can investigate on this further.
Hi There! I am looking to do something similar!
I am currently breaking down an SLA report by hours but with decimals.
I created this report that shows when a ticket was flagged as urgent and when it was marked as "Solved". We do this because we have an internal SLA to solve any urgencies within a specific time frame. Let's say that is 48 hours as an example. I am trying to add conditional formatting that will highlight everything that is greater than 48 hours in red. When I do this the conditional formatting removes my decimals place of 2. I tried adding to the equation to look like so and it was marked as OK but it still doesn't pick up the 2 decimal places. Any thoughts?
IF (SUM(Priority Urgent hours to solved) > 48) THEN
{
"backgroundColor": "#ff726f",
"precision": 0,
"scale": 1,
"prefix": "",
"decimalPlace": "2",
"decimalSeparator": ".",
"italic": FALSE,
"bold": FALSE,
"suffix": " hrs",
"fontColor": "",
"thousandsSeparator": " "
}
ELIF (IS_NAN(SUM(Priority Urgent hours to solved))) THEN
{
"backgroundColor": "#dddddd"
}
ELSE
{
"fontColor": ""
}
ENDIF
This is before the conditional formatting:
and after:
我认为这是因为包容的"precision": 0in your list (you'd want"precision": 2相反),我不认为“decimalplace”a valid argument. SeeChanging the format of resultsfor a list of valid arguments you can include.
Thank you Dave!! That did the trick!!
Is there a way to apply conditional formatting to only certain rows or columns?
For instance, I have a two-way table and I want open tickets belonging to a specific assignee to be red if they have more than 5:
This doesn't seem to do it:
IF ([Ticket status]="Open" AND COUNT(Tickets)>1 AND [Assignee name]="Daniel Williams") THEN
{
"backgroundColor": "red"
}
ENDIF
Even just limiting the IF to [Ticket status]="Open" doesn't mark that column red, or [Assignee name]="Daniel Williams" also doesn't mark it.. can you filter based on the column or row value?
I think that should be possible.HereI see an example on how to change the background colour based on IF/ELSE conditions.
I also think thisarticlemight help you out. You can find interesting resources related to writing Explore formulas.
All of the examples noted are based on the value the cell contains, rather than the value of the column/row it exists in. Can you give an example where the background of a particular cell is adjusted based not on the value of the cell itself, but of the row or column value it resides in?
thank you for sharing these specific details. They were quite helpful in narrowing down your question.
As it is now, it is only possible to apply the conditional formatting to the metric itself, not the row/column in which said value exists, I'm afraid.
Oi Pessoal, tudo bem?
总adicionando Consigo alterar软木somente做uma condição?
Por exemplo, se a quantidade de tickets for maior que 10, quero que o total fique vermelho.
Pleasesign into leave a comment.