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 report. 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 report will only list the true results. In most cases, it is easier to add attributes to the Filters section of the report, 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 report 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 report, 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 report.
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.
15 Comments
Graeme, Eugene,
Hi guys,
It seems that using attributes values directly in formulas is not working for proper calculations (I just receive all tickets instead of intended tickets with the non-blank value in my custom field).
You are writing about using attributes in the formula, but showing examples with tags of those attributes. Is this the proper way to build metrics for custom ticket fields? The documentation is not clear here or do I miss something?
Hi Andrei Kamarouski, could you try removing the quotes from "NULL"? NULL isn't a value, it's essentially "nothing" :-)
There's a similar example of what you're trying to do in this article.
I hope this helps, but do let us know if you need more help. Thanks!
Hi @..., I see now! =) Thx! 😊
I'm not sure if I'm doing this totally correct or if I need to make a whole different set of custom metrics to pull this off, but I'm trying to add some custom fields to my Organizations for "Service Agreements". Each agreement, of which there are 3 for each organization possible, would have a "Contract Number", "Hours Purchased", and a "Contract Value". I need to pull in Hours Purchased and Contract Number in order to run a query against how many "hours billed" against the "Hours Purchased". I've been able to do this easily with just one contract, but now its causing some erroneous data when we have multiple contracts involved. SO I want a field thats SUPER smart. I need one that knows to show hours purchased relevant to which Contract Number is used. The matching field is called "Ticket Sage Reference" (also custom) and lives in the ticket. I want to say something like
IF ([Ticket Sage Reference]=VALUE(ContractNumber))
THEN 1
ELSE IF ([Ticket Sage Reference]=VALUE(ContractNumber2))
THEN 2
ELSE IF ([Ticket Sage Reference]=VALUE(ContractNumber3))
THEN 3
ENDIF
Then one that would ACTUALLY display the content
IF ([CustomMetric]=1)
THEN VALUE(ContractNumber1)
ELSE IF ([CustomMetric]=2)
THEN VALUE(ContractNumber2)
ELSE IF ([CustomMetric]=3)
THEN VALUE(ContractNumber3)
ENDIF
I'm terrible with functions, please send help . I think I may even be at the point where I may even pay Zendesk to write this for me, but wanted to see if anyone could steer me in the right direction before i did that.
Hi Adam Wideman, thanks for the question. I'm not sure if you can do exactly what you need here. However, I really think it would be worth you asking this in our Explore Q and A section at
https://support.zendesk.com/hc/en-us/community/topics/360001200893
A lot more customers will see it there and it's always possible that someone has worked out a solution to this. Thanks!
I have a question, I am trying to build a query for unsolved tickets with ID as well as the number of days the ticket has been open. Would this be a IF-THEN-ELSE calculation or is there an attribute that will give me these results?
Hi CaSonya,
Please check out the Explore recipe: Reporting on the duration of fields as that would provide you the time a ticket has been set to a specific status throughout its lifecycle. Hope this helps! :)
Product: Support
Dataset: Updates History
I have managed to create a calculated field that would display a value of "1" when the Breached SLA Status is "0" with the following formula:
IF (D_COUNT(Breached SLA tickets)=0) THEN 1 ELSE 0 ENDIF
Is there a way for us to get the sum of that value? I am trying to get the weighted average of this metric to get the calculated SLA% per Ticket Group. However, I could not get a SUM function to work. I could not use the same calculation using "SLA target status" as this is not a Metric.
I played around with other Explore functions and I also wasn't able to build a similar metric that works with the SUM aggregator. A workaround that I can suggest is to build a custom Tickets metric under the SLA dataset –
– and then another metric to subtract the count of breached SLA tickets from the total count of tickets.
This should give you the count of tickets without any breached SLA. You'll be able to slice the results from this metric by ticket group or other attributes.
Hi Gab,
I was able to use the calculated metrics function you have mentioned to create a new Service Level metric.
Thank you for your insights.
Hi Team,
Need some help here:
IF (VALUE(Full resolution time (min)) <= 60*5) THEN "< 5 hrs"
ELIF (VALUE(Full resolution time (min)) <= 60*24) THEN "< 24 hrs"
ELIF (VALUE(Full resolution time (min)) <= 60*24*2) THEN "< 48 hrs"
ELIF (VALUE(Full resolution time (min)) <= 60*24*7) THEN "< Week"
ELIF (VALUE(Full resolution time (min)) > 60*24*7) THEN "> A Week"
ENDIF
using this calculation, any results that fall in "< 5 hours" are not included in the "<24". Any that are included in <5 and <24, do not appear in <48 and so on. This is not how IF ELSE functions work. The above calculation actually works like this:
IF (VALUE(Full resolution time (min)) <= 60*5) THEN "< 5 hrs"
ELIF (VALUE(Full resolution time (min)) > 60*5 AND VALUE(Full resolution time (min)) <= 60*24) THEN "< 24 hrs"
ELIF (VALUE(Full resolution time (min)) > 60*24 AND VALUE(Full resolution time (min)) <= 60*24*2) THEN "< 48 hrs"
ELIF (VALUE(Full resolution time (min)) > 60*24*7 AND VALUE(Full resolution time (min)) <= 60*24*7) THEN "< Week"
ELIF (VALUE(Full resolution time (min)) > 60*24*7) THEN "> A Week"
ENDIF
This is incorrect behaviour.
Can you advise how i can have the first calculation working CORRECTLY? I want it to return the values I am asking for for my dashboard chart
My first idea was to simply do multiple IFs, but your columns do not support this. It has to be one calculation, not multiple.
Also, WHY cant you sort the results by the order in which I have provided them in the calculation? A-Z, Z-A, and by totals is not what I want. I want the results displayed the same order in which the formula is specifying
That's right, that's how IF THEN ELSE function works in Explore's query builder – the next ELIF statement will only be considered if the the previous statement did not succeed; if a condition is true, it skips the succeeding conditions and executes that statement. I'm afraid there's no way around this to make Explore return the values for all the IF/ELIF statements that are true.
An alternative that you may explore is to create separate attribute for each condition and have the attribute return True or False (or any string) depending if the ticket meets the conditions. With this, you'll have multiple columns for each brackets, indicating whether the ticket meets the condition statement.
About the order of values – a workaround is to create another custom attribute (an ordered set) to make the report display the values in the order that you want. I agree that it would be much more convenient to have the attribute values to be by default ordered the same way the conditions are listed in the formula. If you have time, we would really appreciate if you can create a post in the Product feedback topic in our Community, about any of your feedback or feature request. The Product team actively monitor the threads, and that would be the best channel to engage with other users with similar needs.
Thanks Killa!
Hi Team,
Need your help in building an attribute function like,
If "A' is Blank then "B", if "B" is blank then "C",If "C" is Blank Then "D" else "A"
I tried to build using below but it only returns value of B when A is blank,
IF ([A] = NULL) THEN
[B]
ELIF ([B] = NULL) THEN
[C]
ELIF ([C] = NULL) THEN
[D]
ELIF ([D] = NULL) THEN
[A]
ENDIF
Thanks,
Tejas
Explore executes the formula based on the first condition that it finds TRUE. So if the condition [A]=NULL is met, then it no longer checks the succeeding statements and immediately returns the value [B]. You will need to add more conditions to each line to make the attribute behave the way to intend it to. Maybe something like:
IF [A]=NULL AND [B]!=NULL THEN [B]
ELIF [A]=NULL AND [B]=NULL AND [C]!=NULL THEN [C]
ELIF [A]=NULL AND [B]=NULL AND [C]=NULL AND [D]!=NULL THEN [D]
ELSE [A]
ENDIF
Thanks Gab, that's helpful.
Please sign in to leave a comment.