Using the IF THEN ELSE function

Return to top

25 Comments

  • Andrei
    Community Moderator
    The Wise One - 2021

    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? 

    0
  • Rob Stack
    Zendesk Documentation Team

    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!

    0
  • Andrei
    Community Moderator
    The Wise One - 2021

    Hi @..., I see now! =) Thx! 😊 

    1
  • Adam Wideman

    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.

    0
  • Rob Stack
    Zendesk Documentation Team

    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!

    0
  • Permanently deleted user

    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?

    0
  • Elaine
    Zendesk Customer Care

    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! :)

    0
  • Sigmund Domingo

    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.

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi Sigmund,
     
    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. 
    1
  • Sigmund Domingo

    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.

    0
  • Killa Kam

    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

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi Killa,

    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!
    0
  • Tejas

    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

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi 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

    Will this work for you?
    1
  • Tejas

    Thanks Gab, that's helpful.

     

    0
  • Colin Hutzan

    I am having a very hard time getting a correct syntax for a calculated attribute I am trying to build.

    I am trying to build an attribute for platform type based on organization IDs. We have several organization IDs that are representative of customer platform. This is a static number as we'll never have more customers on that platform. I have collected all of the IDs in question and I've spent hours trying to get a calculation to work properly. I either get a syntax error or I get results on reports where the calculation only worked on the first organization ID (there are about 50)

    I have tried (I eliminated many org IDs to keep the following short):

    IF ([Ticket organization ID] IN 
    ("361422105392",
    "361422105592”,
    "361683482671”))
     THEN
        "Open Regulate"
    ELSE
        "TCII"
    ENDIF

    IF ([Ticket organization ID] = 
    "361422105392",
    "361422105592”,
    "361683482671”)
     THEN
        "Open Regulate"
    ELSE
        "TCII"
    ENDIF

     

    IF ([Ticket organization ID] = 
    ("361422105392", OR
    "361422105592”, OR
    "361683482671”))
     THEN
        "Open Regulate"
    ELSE
        "TCII"
    ENDIF

     

    IF ([Ticket organization ID] = ("361422105392") OR

    [Ticket organization ID] = ("361422105592") OR
    [Ticket organization ID] = ("361683482671"))
     THEN
        "Open Regulate"
    ELSE
        "TCII"
    ENDIF

     

    I need to segment our ticket analytics by platform type. What am I doing wrong with this calculation?

    0
  • Zsa Trias
    Zendesk Customer Care

    Hello Colin,

    I looked into the formulas you tested and I feel like the last one should have worked.
    You may have to double check if you've input the correct organization IDs in your formula. 

    Here's also another formula that you can try if you want to use the IN function:

    IF IN([Ticket organization ID],ARRAY("361422105392","361422105592","361683482671"))
    THEN "Open Regulate"
    ELSE "TCII" ENDIF

    Hope this helps!

    0
  • Colin Hutzan

    Thank you! I ended up getting what I needed by creating calculated groups instead of attributes.

    0
  • Test User Migration 1

    I am currently in a dilemma with my custom calculated attribute as it is giving an incorrect result. Could you please help me with this? It is giving me "Achieved SLA" even though the result should be "Not Achieved SLA".

    IF ((([Ticket type]="Incident" AND [Ticket priority]="High" AND SUM(% Achieved SLA tickets)<99.5))) THEN "Not Achieved SLA"
    else "Achieved SLA"
    ENDIF

    0
  • gan ganor

    I want to see how many tickets were handled by an agent on a certain date. The date of the update has to be the same as the ticket creation date.

    Right now, I have a report that shows me the date of creation, but the update that's related to that agent can be on a different date. Also, often the update can be omnichannel assigning the ticket to the agent, but it's not an actual update by the agent himself.

     

    Essentially, I want a report that by filtering a date, will show me how many tickets an agent did that day (It's a live chat report, so we want to see updates that are relevant to the date of creation)

    0
  • Leo Medalla

    I've been playing around with this recipe. We are trying to track the requester wait time for specific Custom Field or Ticket Form. Recipe below.

    IF (([Case Type]="Technical Inquiry") OR ([Ticket form]="Support - General Support ")) THEN [MIN(Requester wait time (min))] ENDIF

    My team will appreciate all the help. Thank you.

    0
  • Mike DR
    Zendesk Customer Care
    Hi Gan!

    For ticket updates, you would need to use the Update history dataset. The difference would be explained here: What is the difference between the Tickets, Updates history, and Backlog datasets?
    0
  • Shivam Garg

    i was using the following sytax to get the date when the implementation status was closed , 

    although the format is being accepted but still i am not able to see any results in the dashboard.

     

    the new calculation reforms are preventing us from returning the date values.

     

    IF ([Changes - Field name]= "Implementation Status"
    AND [Changes - New value]= "imp_closed"
    AND DATE_LAST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name],[Changes - New value])=[Update - Timestamp])
    THEN [Update - Date] 
    ENDIF

    0
  • Nestor Vergano

    Hi!

    I'm trying to identify those tickets that had been received before the end of the the day (Mon-Fri before 18:00) BUT had been left over (from New to Open) till the next day.

    This is the script I wrote, but an error "There's an issue with the formula..." appears with no more precise info. The highlighted code is the one that brings the formula error, and I don't understand why.

    Please Help! Thanks in advance

    IF (IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday"))
        AND IN([Ticket created - Hour],ARRAY(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)))
    THEN IF ([Ticket created - Date] != [Ticket first assigned - Date])
                THEN TRUE
                ELSE FALSE
    ELSE FALSE
    ENDIF

    0
  • Elaine
    Zendesk Customer Care
    Hi Nestor,
     
    I see that there is an indeed error with the calculation you have shared. Please see the attached screenshot.
     

     
    You need to update it using this code
     
    IF (IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday"))
    AND IN([Ticket created - Hour],ARRAY(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)))
    AND ([Ticket created - Date] != [Ticket first assigned - Date])
    THEN TRUE
    ELSE FALSE
    ENDIF

    I trust this proves beneficial!
    0

Please sign in to leave a comment.

Powered by Zendesk