The IF THEN ELSE function is used in calculations in several different ways, including filtering, grouping, bucketing, and relabeling results. The IF THEN ELSE function tests a condition, then returns a value based on the result of that condition.
The IF THEN ELSE expression can be defined in two ways:
- IF (boolean condition) THEN (true value) ELSE (false value) ENDIF: The returned result will depend on whether the condition passes or fails.
- IF (boolean condition) THEN (true value) ENDIF: The returned result will always be the true result. If the conditional expression fails, results will be empty.
Additionally, you can nest multiple IF THEN ELSE statements. For more information, see Nesting multiple IF THEN ELSE functions below.
For information about all of the available functions in Explore, see Explore functions reference.
Grouping your results
You can use the IF THEN ELSE function to group or bucket your results. This enables you to relabel, exclude, or segment results to indicate higher or lower values. When you combine grouping and filtering together, you can highlight results by individual values.
This section shows how to relabel your results based on the conditional expression's outcome.
Labeling your grouped results
You can use the IF THEN ELSE function to apply different labels to your results, depending on the outcome of your conditional expression. For example, you can use IF THEN ELSE to label whether results are above or below a metric's target. This can be useful for quickly identifying if your headline number surpassed a goal.
This example uses the IF THEN ELSE function to indicate whether an agent was above or below the targeted first reply time. You can duplicate this example using any attribute, metric, and target.
To label results based on first reply time
- In the Calculations (
) menu, click Standard calculated attribute.
- Under Functions, click Add.
- Give your calculated attribute a name like First reply time test.
- From the list of functions, choose IF THEN ELSE.
- Double-click _boolean_condition to highlight it.
- Select a metric from the Select a field drop-down list or type in the metric name. This example uses SUM(First reply time (min)).
- Type in the greater than symbol (>) and your target amount.
- For _value_if_true enter "Over" in double quotes.
Note: You can also substitute in elements or other labels. For example, you could select your Invoice Number attribute for your _value_if_true, to view the invoice numbers for the values with revenue above 100,000.
- For your _value_if_false enter "Under" in double quotes.
- In Computed from, select the attribute you are using in your query. If you do not select an attribute, the calculation will be measured against the total.
- Click Save.
- Click Add in any attribute panel.
- Choose your attribute from the attributes drop-down list.
Filtering your results
You can filter your results with IF THEN ELSE by removing the ELSE statement. When you remove the false parameter, your query will only list the true results. In most cases, it is easier to add attributes to the Filters section of the query, but if you want to calculate results before processing or perform unique calculations, you can use this method.
Filtering results by metric
You can filter your query to only show results that are higher or lower than a specified metric value. For example, you can limit your results to only display assignee names with a first reply time over 10 minutes. Follow the same steps as the labeling example above, but with this formula:
IF (SUM(First reply time (min))>10 THEN [Assignee name] ENDIF
When you add the attribute to your query, only the assignees with a first reply time over 10 will be displayed.
Filtering calculations by attribute
Along with filtering results based on a metric, you can also filter calculations to a specific attribute value. This formula is useful if you want to perform calculations on one attribute value and show the original metric results, or perform different calculations on the other values.
The example below calculates the number of replies per ticket for the support ticket tag, but you can duplicate this example using any attribute value and calculation.
To limit calculations to an attribute value
- In the Calculations (
) menu, click Standard calculated metric.
- Name your calculated metric.
- Under Functions, click Add.
- From the list of functions, choose IF THEN ELSE.
- Double-click _boolean_condition to highlight it.
- Select the attribute containing the restricting value from the Select a field drop-down list or type in the attribute name. This example uses Ticket tags .
- Type in an equal sign (=) and the attribute value in "double quotes". This example uses ="support".
This conditional expression will restrict your calculation to the entered attribute value.
- In _value_if_true enter your calculation.
- Delete ELSE and value_if_false. You can use value_if_false to provide an alternative formula for results when your expression fails. This example does not use a false option, so the ELSE statement is deleted.
- Click Save.
- In the Metrics panel, click Add, then choose the calculated metric to add to your query.
Nesting multiple IF THEN ELSE functions
If you are using more than one ELSE IF statement in your formula, you can use ELIF to simplify your expression. For example, if you are trying to show different numbers when your first reply time is greater than 10, equal to 10, or less than 10, you could use the ELIF expression to avoid writing multiple ELSE IF statements.
The conditional expression for this example would look like the formula below:
IF (SUM(First reply time (min))>10) THEN 1 ELIF (SUM(First reply time (min))=10) THEN 2 ELIF (SUM(First reply time (min))<10) THEN 3 ENDIF
Additionally, you can add an ELSE statement after the conditions. The ELSE value will be used if none of the conditions are true.
Nesting multiple conditional IF THEN ELSE statements can be useful for creating several different groups or filtering by different conditional expressions.
For another method you can use to evaluate multiple conditional expressions, see Adding multiple conditional expressions with SWITCH.
38 Comments
Hi,
I'm trying to use an IF statement to set static KPI targets by priority but I am getting no data.
Here is the statement I was trying to use:
IF ([Ticket priority] = "Urgent") THEN
24
ELIF ([Ticket priority] = "High") THEN
48
ELIF ([Ticket priority] = "Normal") THEN
72
ENDIF
In other words, I want to show last week's first reply times in a table as they compare to our target first reply times.
Can anyone help or share some insight?
Thanks :)
Hey there Ayal,
This might require a bit more digging into to properly assess the report you're trying to build, as there are a few questions I need to ask you. I'm going to pull this into a ticket so we can continue the discussion!
Thank you for your patience,
Hi there
I'm trying to calculate NPS score using a custom numeric field I made in Support. I enter the number between 0-10 manually in a custom user field. If I assign 9 or 10, they should be considered a 'promoter', 7-8 should be a 'neutral' and the rest should be 'detractor'. I then want to perform the following calculation in Explore:
Promoter % – Detractor % = NPS
I guess I need to make this using an IF statement, followed by a custom metric, but I'm not sure how. Here's where I'm at so far. Any help is appreciated!
Amazing, thanks Rob!
Forgive my beginner questions, but does this still work if the custom field 'NPS Score' is on the user and not on a ticket?
Also, when I tried to make it, NPS Score wasn't an option in the first formula (I haven't got beyond the first one yet). I did get offered NPS comments instead by autocomplete, which is a text box custom user field I added.
Glad it helped and it's really not a beginners question!
I did a quick experiment and you might be able to use [Requester ID] instead of [Ticket ID]. You would need to set the aggregator to DCOUNT (which will only count each requester once).
That would look at all users who requested tickets, but only those with an associated NPS score. See if that works for you!
Thanks Rob! That makes sense.
I'm not being offered NPS Score in the formula maker - only NPS comments and NPS category (two non-numeric custom user fields I made) so I'm not sure what is happening. It lets me make the custom metric but doesn't recognise [NPS Score] then presents an error when I add the metric to my new query
Can you see if it's available if you use a custom attribute?
From this article, it depends on how the field is configured. You mentioned that you enter it manually. If you type it in, it might have been created as an attribute. See:
https://support.zendesk.com/hc/en-us/articles/360022182014-Reporting-with-custom-fields
If you can't find it, we'll open a ticket so someone can dig into this a bit further with you :-)
Thanks, Rob. The screenshot above is when I click on metrics, so it's showing there as a numeric custom field, but doesn't show when I start typing NPS in the formula sentence. The other two custom fields I mentioned are showing when I select to add an attribute, so they all seem properly sorted at that level.
Thanks for your help!
Thanks Fiona! That clearly seems like it shouldn't be happening. I've opened a ticket for you as I'd love us to help you get to the bottom of this!
Amazing, thank you!
Hello,
Can anyone help me with the following.
Is there a limit to how many else if statements can be used in a macro in Zendesk. When I add a second else if statement to the code below. A new ticket is not picking up the brand and is displaying the wrong URL.
The code works and I can save the macro.
{% if ticket.brand.name == 'xxx' %} Deactivation Form {% else if ticket.brand.name == 'xxxx' %}Deactivation Form {% else if ticket.brand.name == 'xxxxx' %}Deactivation Form {% else %}Deactivation Form{% endif %}
Thank you in advance.
How do I check against blank values when writing if then statements? I have tried statements with = 0, NULL and NaN but cannot get the then statement to work. For example:
IF SUM(Downtime in Minutes) = 0 THEN 100
ELIF SUM(Downtime in Minutes) = NULL THEN 100
ELIF SUM(Downtime in Minutes) = NaN THEN 100
When I look at SUM(Downtime in Minutes) I may get no data as a result, I would then expect this if then to return 100 but that is not happening. I've also tried = "" and = " " but still no 100.
What can I do to have a no value return 100? I am trying to solve for when we, in the past present or future do not put a value in this numeric field. If there is no value I can assume uptime was 100%.
Hello Kris,
If you use IF VALUE(Downtime in Minutes) = NULL THEN 100 it should work for you. The SUM function is not adding anything together here so it is throwing off the metric.
I hope this helps!
Best,
Chris H
Hello Chris,
Thank you for your response. I tried your suggestion and I am still getting blank values as a return item.
I have this:
IF VALUE(Downtime in Minutes) = NULL THEN 100
ELSE (44640 - SUM(Downtime in Minutes))/((44640 - SUM(Downtime in Minutes)) + SUM(Downtime in Minutes)) * 100
ENDIF
but I am only getting values for the ELSE part of the statement.
I also tried using:
IF VALUE(Downtime in Minutes) = 0 THEN 100
ENDIF
And only get partial success:
Am I doing something wrong? I would expect the above blanks to have a 100 in the values.
Hello Kris,
It looks like you are being helped in an Advocacy ticket. If you need further help or have other questions on Explore, please let us know.
I'm having the same issues as others. If I include an ELIF clause only the ELSE values are being shown (URLs are just examples, they're going to link to ticket views):
IF (VALUE(Support Priority)=NULL) THEN LINK("https://www.zendesk.com",0)
ELIF (VALUE(Support Priority)="1") THEN LINK("https//www.google.com",1)
ELIF (VALUE(Support Priority)="10") THEN LINK("https//www.google.com",10)
ELSE LINK("https://supportdriven.com",2)
ENDIF
Even on a report where it's shown next to another calculated attribute of simply VALUE(Support Priority), which shows correctly, it only ever shows the ELSE values.
Is there a wider issue here? It seems a number of people are having the same issue.
Matthew
If you create a calculated attribute in the following format, the IF conditions should work as expected:
So can you please tell me about your 'Support Priority' field? Is this a custom text or numerical field?
Hi Graeme,
That’s right, “Support Priority” is a custom numerical field that we fill via the API according to some internal metrics. Because of this we have to set it as a calculated attribute and we want to link each category to a different place.
Matthew
I cannot give a full explanation, but I suspect it is something to do with the level your custom numeric field is being aggregated.
Forcing the calculation at ticket level seems to resolve this:
Thanks Graeme! It may have been that, but your comment also made me realised that we have two fields with the same name - one on the user and one on the ticket - and it's not obvious which is which when working in the formula box. I'd just been using the first one that came up so I switched to the second one and now it's working!
Thanks for your help 🙌
PS. It'd be really great if it were more obvious which object those fields were on, for cases like this.
Matthew
Glad you are sorted. I certainly needed to use the 'Computed from' setting, otherwise I just got 'Other' as my result. Hopefully when we are up to speed with Explore these things will become more oblivious :)
Hi Mike P, thanks for this feedback. ELIF is documented at the end of the article - https://support.zendesk.com/hc/en-us/articles/360022367153?page=1#topic_xwz_smg_lx
and ELSE should work as you state. However, you are right that the article could be clearer.
I'll be doing some further testing this week and will make some updates to the article soon.
Hi Mike P have you seen the Explore functions reference article? - https://support.zendesk.com/hc/en-us/articles/360022184834
This contains a listing of every Explore function with a brief description. Some of the functions need further explanation so we create separate articles for those.
However, we are always looking at ways to improve the documentation based on what people using the product need, so please keep the feedback coming!
Hi Rob,
I'm trying to show multiple ticket creation by individual requesters.
I would like to work out how to calculate, out of the total requesters, the number who have submitted one ticket, two tickets and so on.
Ideally I would like to be able to show this as a proportion, with % of requesters subimtting one tickets vs requesters submitting more than one ticket,
Many thanks for the help.
Hi Chris , here's one way of doing what you want.
Open a query using the Support: Tickets dataset.
In the Metrics panel, add COUNT(TICKETS)
In the Columns panel, add Requester name
Change the visualization type to a Pie chart
That will give you what you are looking for. Hope this helps!
Hi Rob,
Thanks for that. Tried but unfortunately,that doesn't quite solve my problem. As we have over 1000 people submitting tickets, the pie chart doesn't really work
I wonder if it might need another step, a way of creating a custom standard metric where requester IDs that have multiple tickets attached are given a certain value, and those who have a single ticket attached are given a different value.
I'm had read this thread about repeat ticket submitters - I wonder if there is a similar solution?
https://support.zendesk.com/hc/en-us/community/posts/203458196-GoodData-tip-Track-your-rate-of-repeat-ticket-submitters
However the steps carried out in this thread are per 3 years old and I think it refers to the previous system.
Thanks!
Hi Chris I read through that article, but wasn't able to create it in Explore. I've opened your query as a ticket, so support can work with you to try and get exactly what you need. Thanks!
Hi,
I'm wanting to record how many incidents are transferred from one department to another, so say I have a transfer from our Support team with ID 360001471000 to our EDD Team 360001470980 to provide me the updated Ticket ID, what would be the best way to achieve this?
We're wanting to avoid the Ticket Tags reporting as when this is transferred back from EDD to Support we lose the Tag.
Thanks!
Hey Oliver,
I was able to track down the following recipe that could help get you started on the report you're looking for:
Explore recipe: Tracking ticket assigns across groups
I hope this points you in the right direction!
Please sign in to leave a comment.