Writing Explore formulas

Return to top

21 Comments

  • Chris Wilbur

    Hello!

    Is there a way to add a wildcard into a formula? As a hypothetical, say I have a custom field that lists the country name and I want a report that filters just countries starting with "united" (United States, United Kingdom, etc). Is there a symbol I can use, such as below:

    IF ([Country]="united%") THEN [ticket_ID] ENDIF

    0
  • Graeme Carmichael
    Community Moderator

    Cwilbur

    You can use the STARTSWITH(_text,_text_to_search) function to do that.

    IF STARTSWITH([Country],"united"]) THEN [ticket_ID] ENDIF

    There are a few other similar text functions such as:

    • CONTAINS(_text,_text_to_search)
    • ENDSWITH(_text,_text_to_search)
    • FIND(_text,_text_to_find,_number_start_index)
    • LEFTPART(_text,_number)
    • RIGHTPART(_text,_number)

    The full list of text functions is here.

    0
  • Katie True

    Hi, I'm trying to replicate the following Insights custom metric in Explore and can't seem to get the formula correct. Any advice?

    Insights: SELECT AVG([Text Field] Duration in minutes)/60 WHERE Text Field = Group AND Ticket Status <> Deleted
     
    I tried creating a standard calculation in the Tickets dataset, but only got this far (without getting the green checkmark) 
     
    Explore: SELECT([Changes - Field name]AVG(Field changes time (hrs))) WHERE [Changes - Field name] = [Update ticket group]
    0
  • Ahmed Mamdouh
    what to create like this metric through exploer 
    SELECT COUNT (Ticket IdSatisfaction Survey Change) WHERE Ticket Satisfaction Score IN (BadGood) AND Ticket Status <> Deleted

     

    1
  • Erin Miller

    I'm trying to create a query that is basically - MUST HAVE Tag 1, and then display all other tags.

    So end result is showing me how many tickets have tag 2, have tag 3, have tag 4, etc. but filtered on only those that ALSO have tag 1.

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Erin Miller. I believe this custom metric will help. It's from the article Reporting with tags.

     

    IF (INCLUDES_ANY([Ticket tags], "tag 2","tag 3","tag 4")) AND INCLUDES_ALL([Ticket tags], "tag 1") THEN [Ticket ID] ENDIF
    0
  • Erin Miller

    Thank you! I am confused though, because I don't want to have to list out all the possible tickets there can be (tag 2, tag 3, tag 4) as those are ever changing. So its really "everything with tag 1" but then what I'm trying to do is count the number of tickets of each other type that has ticket 1. Does that make sense?

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Erin Miller, here's another way around to try this. It should be a good starting point to get to what you need.

    Create two standard calculated attributes.

    The first has the formula:

    IF INCLUDES_ALL([Ticket tags], "Tag 1") THEN [Ticket ID] ENDIF

    And the second has the formula:

    IF INCLUDES_ANY([Ticket tags], "Tag 2", "Tag 3","Tag 4") THEN [Ticket ID] ENDIF

    (you might want to create three copies of that one so you can report on each tag separately).

    Now, add the Tickets metric to the metrics panel and the two (or more) attributes you created to the Rows panel.  You'll end up with something that looks like this:
    You can then add further attributes to give you more information. I hope this works as a starting point for what you need!

    0
  • Erin Miller

    No, this still has the same problem. There is not a finite list of Tag 2, Tag 3, Tag 4... that I want to detail out in this procedure, as I do not want to rebuild this report every time more tags are are created it would be unscaleable the way we use zendesk.

    What I'm really looking for is:

    Show me all tickets with tag 1. Then DISPLAY FOR ME the number of tickets that have each and every other tag we have available. so end result will be:

    100 tickets total with Tag 1. of those 100 tickets:

    30 tickets have tag 4

    15 tickets have tag 19

    17 have tag 21

    etc

    0
  • Emily Czechowski

    Hi all, I am wondering if it is possible to create a metric, attribute or query to identify tickets that contain certain words or strings of words? i.e. Get ticket numbers for all tickets that contain the words "alpha" and "beta". Must contain both to return ticket ID. How would I do this?

    0
  • Graeme Carmichael
    Community Moderator

    Emily

    The ticket subject line can be checked for text, but ticket comments are not available in Explore.

    To check the subject for both alpha and beta in any case, use a custom metric:

    If you need to check if the phrases appear in the ticket comments you would need to set a triggers to check for each word. The trigger will set a tag if the word is present. You then create a metric to check for the tags.

    0
  • Katie Barton

    Hello,

     

    Restating this question from 5 months ago. I'm trying to replicate the following Insights custom metric in Explore and can't seem to get the formula correct. Any advice?

    Insights: SELECT AVG([Text Field] Duration in minutes)/60 WHERE Text Field = Group AND Ticket Status <> Deleted
     
    I tried creating a standard calculation in the Tickets dataset, but only got this far (without getting the green checkmark) 
     
    Explore: SELECT([Changes - Field name]AVG(Field changes time (hrs))) WHERE [Changes - Field name] = [Update ticket group]
    0
  • Lauren Jeffreys

    I am trying to create a standard calculated attribute that returns tickets that have two tags. I am using the recipe above for that, but it is still returning tickets that have one of those two tags. 

    I need the report to show only if the ticket is tagged with both cashorder AND contract_stage_1 but the recipe is allowing tickets that have one or the other, but not both. 

    IF (INCLUDES_ALL([Ticket tags], "cashorder","contract_stage_1")) THEN [Ticket ID] ENDIF

     

    0
  • Marco Malbas
    Zendesk Customer Care
    Hi Lauren. Thanks for reaching out regarding this. I'd like to take a look at this further for you but would just like to clarify if you are indeed using it as a standard calculated attribute? Using this as a metric should work fine, and with your description that it would return tickets with two specific tags, this would be a metric instead of an attribute. Can you try creating it as a standard calculated metric instead? More information can also be found here: https://support.zendesk.com/hc/en-us/articles/4408838151450-Reporting-with-tags#topic_q5d_dms_jkb
     
    Cheers! 
    0
  • Delaney F.

    Hi Zendesk Team -- I am using the SUM (% Achieved SLA) metric to track our achievement rate over the last 30 days. 

    I want to add a constant threshold, let's say at 90% for example, and have it added as a horizontal line on this graph. I would then like to calculate the amount of time we are above this threshold for the given time period (past 30 days in this example). I would like to be able to adjust the threshold, i.e. change it from 90% to maybe 95% in the future. Please advise. Thanks!

    2
  • Dane
    Zendesk Engineering
    Hi Delaney,
     
    You can use trend lines to designate a constant value for your report. Refer to the screenshot.
     

     
    When it comes to the formula that you have requested, there's no native function in Explore that can return the timestamp at which the metric exceeded (or fallen below) a trendline.
     
    Hope this helps.
     
    Cheers,
    Dane
    1
  • James Beniston

    Hi Everyone,

    I am trying to report on our onboarding efforts with customers, all the data on which is stored in Zendesk.

    All of our onboarding process is handled via a single ticket per organization. I would like to count all of these tickets, i assume this could be done via creating a new standard calculated metric?

    Something like: COUNT(Tickets)[Ticket form]="Customer Onboarding"  

    This one does not compute, i must be missing something...

    What I would like to do then is slice the data on standard calculated attributes to say that:

    • xx% of all onboarding tickets either receiving an onboarding call // refusing an onboarding call 
    • or xx% of tickets did not hit time to value

    I feel like i need the metric first an the rest will flow into place.

    Any input on this would be awesome!

    Cheers

    James

     

    1
  • Gab Guinto
    Zendesk Customer Care
    Hi James,
     
    Do these tickets have Customer Onboarding selected as their ticket form? If so, then you can try this custom metric formula:
    IF [Ticket form]="Customer Onboarding" THEN [Ticket ID] ENDIF
    This should give you the count of tickets with that selected form. You can compare it to or make further calculations along with the total count of tickets or your other custom metrics.
    1
  • James Beniston

    Thanks Gab Guinto, i have what i need!

    0
  • Raghul P

    IF ([Ticket status]= "Open"AND NOT INCLUDES_ANY([Ticket tags], "tag1","tag2")

    AND INCLUDES_ANY([Ticket tags], "tag1"))

    THEN [Ticket ID] ENDIF

    Under Standard Calculated Metric, the above formula shows as correct, but it is not producing any result, is this a correct form? 

    0
  • Alex Zheng
    Hey Raghul,
     
    I will open up a ticket with you to take a look at your formula.
     
    Best regards,
    0

Please sign in to leave a comment.

Powered by Zendesk