Writing Explore formulas

Return to top
Have more questions? Submit a request

31 Comments

  • Quentin

    How do you use Attribute values?

    For example I want to filter a certain value of an attribute (filter out deleted tickets), in GoodData it looked like this: "Ticket Status <> Deleted"

    0
  • Molly
    Zendesk Customer Success

    Hey Quentin!

    Deleted tickets are already excluded from the Support Tickets dataset so any metrics you make there do not need any additional modifications. If you use the Ticket Updates dataset, deletion events will be included there.

    0
  • Quentin

    Thanks Molly for the explanation.

    But how do you filter by status for example? If I want to see only open tickets in my metric? Something like [Ticket Status]=Open?

     

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Quentin, there are two options.

    1. Without a custom metric. Add the Ticket Status attribute to a panel and then click it. You can include, or exclude the values you want. In your case, you’d select only Open.

    2. Use a custom attribute. You are correct, in your suggestion, but you do need quotes, for example:

    [Ticket Status]="Open"

     

    Hope this helps!

    1
  • Quentin

    Perfect Rob!

    Thanks for the tip :)

    1
  • Paul Stolz

    How can I create a formula to show the end-user phone number in a Query? Currently, there is a row value for End-user email, but not for end-user phone number. 

    0
  • Graeme Carmichael
    Community Moderator

    Paul

    Unfortunately the user's phone number is not part of the dataset so you cannot report on it using Explore.

    In Support, you would have to copy the phone number into a custom user field and then use the custom field in Explore.

    0
  • Gef Faelden

    Can you add a comment in your calculated field/metric?

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hello Gef Faelden,

    My recommendation would be to use a datatip to accomplish this. I've shared a link below to an article that explains how to implement these and its range of uses.

    Working with datatips

    Best regards. 

    0
  • CS Analyst

    Hi,

    Comparing to the Insights, how to express metric formula with this logic in Explore?

    I can find the Ticket Tags in Fields but I can't find the tag "category_id_175" that i need.

    Regards.

    0
  • Madison Davis
    Zendesk Community Team

    Hi there! I think you'll want to look at Reporting on ticket tags in Explore for help writing this metric. 

    Specifically, you could write a standard calculated metric with the following formula:

    IF ([Ticket tags]="category_id_175") THEN [Ticket ID] ENDIF

    Then filter your query for date ticket created. Unlike in Insights, you can just type the entire formula rather than selecting elements from a menu. Additionally, you can report on this ticket tag without a custom metric, as outlined in the first article on reporting on ticket tags. 

    0
  • David Spies

    I used to have several custom metrics in insights that gathered categories and their sub-categories and listed them as single item to be used in some pie charts.

    For example we have a root category called business process and I want to count any tickets that fall within that category.  the tags are business_process, business_process::requests, business_process::requests::email, etc

    I cannot seem to find a way to duplicate the insights expression below:
    SELECT # Tickets WHERE Custom Attribute Label Category [list] [Category] LIKE "%BUSINESS_PROCESS%"

    Any help would be greatly appreciated.  Thanks!

    0
  • Madison Davis
    Zendesk Community Team

    Hey David! I think you're looking for the recipe here: Explore recipe: Reporting on nested drop-down fields

    1
  • David Spies

    Hi Madison,

    I tried what you suggested in your link, and I was certainly able to create a new query that combined the nested fields into a single item.  Thank you.  My trouble now is that I'm unable to combine multiple of these similar queries into a single pie chart.  Essentially I want to create a pie chart that lists all of the tickets for every category and their nested drop-down lists, but have each separate category represented by a single number.  See the image below for an idea:

    In the past I did this with a custom metric, so how do I do this now with a query instead?

    Thanks for any guidance you can provide!

    0
  • Madison Davis
    Zendesk Community Team

    Got it, thanks David, and sorry for the delayed response! I actually recommend using an Attribute Group instead if you just want to report on the level one nest of the field. 

    0
  • Armon

    Hi,

    I am trying to replicate this Insight recipe on explore and is having difficulties this the AND OR condition

    Insight Recipe - SELECT # Solved Tickets WHERE (Chat Type IN (Complete::Follow upComplete::New Enquiry)) OR ((Chat Duration >20) AND (Chat Type NOT IN (Invalid::B2B/Solicitations/AdsInvalid::Client RequestInvalid::Career/Employment/Internship EnquiryInvalid::Duplicate TicketInvalid::Language BarrierInvalid::Network/System/Technical Error/DowntimeInvalid::Not Business Related TopicInvalid::Nuisance/LewdInvalid::Offline Message/No Entry URL/No Chat AgentInvalid::Out of Coverage/InternationalInvalid::Research/Homework/AssignmentInvalid::Test Chat)))

    Any help would be much appreciated

     

    Thanks

    0
  • Vladimir Petrushenka

    Hi folks, 

    I have here a specific case and was hoping someone could advise. 

    In my query I want to see all tickets that don't contain tags 'tag_1', 'tag_2' and 'tag_3'. I have a Custom attribute that excludes those tickets. However, as a part of my query I also want to include the tickets that contain both tags 'tag_1' and 'tag_4'.

    Would I be able to run such query? If so, would be grateful for the advice on how can I do it. Tried few things but none worked. 

    Thanks! 

    0
  • Jacob J Christensen
    Community Moderator

    Hi Vladimir,

    You should be able to create a calculated metric that counts tickets based on the tags they do or don't have.

    Something like this:

    IF 
    (NOT INCLUDES_ANY([Ticket tags], "tag_1","tag_2","tag_3"))
    OR
    (INCLUDES_ALL([Ticket tags], "tag_1","tag_4"))
    THEN [Ticket ID]
    ENDIF

    ☝️I haven't tested this, so you should definitely verify that it works before relying on it for reporting.

    I hope this helps.

    1
  • Vladimir Petrushenka

    Hi Jacob 

     

    Thanks for getting back to me. That didn't work, but that's because I've just realised that there are other conditions to the query that make impossible to pull the data into one chart. I'll just stick with two separate charts as we previously did. Thanks for checking this for me though! 

     

    1
  • Cwilbur

    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 Mahmoud
    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

Please sign in to leave a comment.

Powered by Zendesk