Writing Explore formulas

Return to top

14 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

Please sign in to leave a comment.

Powered by Zendesk