Using the IF THEN ELSE function

Have more questions? Submit a request

22 Comments

  • Ayal Kellman
    Comment actions Permalink

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

    0
  • Kyle Jones
    Comment actions Permalink

    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,

    0
  • Fiona
    Comment actions Permalink

    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!

     

    0
  • Rob Stack
    Comment actions Permalink
    Hi Fiona, this set me thinking, and I believe I have a way you can do this. I didn’t have any test figures to try this with, but did confirm that the formulas are sound. Consider it a starting point!  If anyone knows a better method to do this, I’m sure they will jump in.
    You’ll actually need to create four custom metrics. Do let me know how this works out for you!
     
    1. Total scored tickets (Total tickets with a score]. (Use COUNT aggregator)
                IF NOT ([NPS Score] = NULL) THEN [Ticket ID] ENDIF      = Total number of tickets with a score
     
    1. Promoter.  (Use COUNT aggregator)
                IF ([NPS Score]>8) THEN [Ticket ID] ENDIF
     
    1. Detractor.  (Use COUNT aggregator)
                IF ([NPS Score]<7 THEN [Ticket ID] ENDIF
     
    1. NPS score.  (I think this should be the SUM aggregator, but you’ll need to test)
                (([Promoter]/[Total scored tickets])*100) - (([Detractor]/[Total scored tickets])*100)
    0
  • Fiona
    Comment actions Permalink

    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.

     

    0
  • Rob Stack
    Comment actions Permalink

    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!

    0
  • Fiona
    Comment actions Permalink

    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

     

     

    0
  • Rob Stack
    Comment actions Permalink

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

    0
  • Fiona
    Comment actions Permalink

    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!

    1
  • Rob Stack
    Comment actions Permalink

    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!

    0
  • Fiona
    Comment actions Permalink

    Amazing, thank you!

    0
  • Anton Van der meyden
    Comment actions Permalink

    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.

    0
  • Kris Scuteri
    Comment actions Permalink

    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%.

    0
  • Chris H
    Comment actions Permalink

    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

    0
  • Kris Scuteri
    Comment actions Permalink

    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.

    0
  • Devan - Community Manager
    Comment actions Permalink

    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. 

    0
  • Matthew Searle
    Comment actions Permalink

    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.

    0
  • Graeme Carmichael
    Comment actions Permalink

    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?

    0
  • Matthew Searle
    Comment actions Permalink

    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.

    0
  • Graeme Carmichael
    Comment actions Permalink

    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:

    0
  • Matthew Searle
    Comment actions Permalink

    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.

    0
  • Graeme Carmichael
    Comment actions Permalink

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

    0

Please sign in to leave a comment.

Powered by Zendesk