Explore functions reference

Return to top

29 Comments

  • Ben Fulton

    Our tickets have a multi-select field that we use to track the ticket escalation path through multiple tiers of support. If a ticket is assigned to one of those tiers, that tier's value will be added to the field for that ticket.

    We would like to build a report that only shows the *maximum* escalation level for each ticket over time. So, for example, if a ticket has the values of both V1 and V2 for the field, we would like to *only* count the ticket on the chart as V2. Currently, when I build charts using this field we see tickets double-counted, showing as both V1 and V2 (where the metric is Count(Tickets) and the Rows are the field in question).

    How would I compose an Explore metric or attribute that only reports a single "maximum" value for a multi-select field for each ticket? 

    0
  • Graeme Carmichael
    Community Moderator

    Ben

    Try this:

    Use the Ticket Updates dataset

    Create a new Standard Calculated Metric to convert your dropdown menu to a number. If your dropdown values look like numbers, you can do this:

    IF [Changes - Field name] ="Dropdown Number" then
    NUMBER([Changes - New value])
    ENDIF

    In the above my custom dropdown field is named 'Dropdown Number' which would be your escalation custom field.

    Now you can use a MAX aggregate to find the highest values:

    So I believe the trick is to get your text drop down field as a number so you can manipulate it to find the highest value.

    0
  • Ben Fulton

    Hi Graeme,

    Unfortunately, our values do not look like numbers, so I tried building a metric that maps the string values to integers via a switch statement, but I am not getting any results.

    IF [Changes - Field name] = "Escalation Level" THEN
    SWITCH [Changes - New value] {
    CASE "Customer Service": 1
    CASE "Product Support": 2
    CASE "Engineering": 3
    }
    ENDIF

    I have verified that the first ticket result does have some updates that should register:

    Am I missing something obvious here?

    0
  • Graeme Carmichael
    Community Moderator

    Ben

    In the Rows section, if you include the field 'Changes- New Value' that will help validate the custom metric.  The values returned here may be different from what you expected when writing your formula and the matching to you SWITCH/CASE statement is case sensitive.

    0
  • Matthew Taylor

    Hi all,

    How can I identify in explore the first Chat EngagenementID where an agent sent more than 0 messages? Ultimately I want to be able to use this to identify the First Engagement Dept for each chat so I can get volume related data for each Chat Department based on the First Dept that handled that chat. 

    I'd use the Chat Department, but this updates to the Final Engagement Department...

     

    e.g. If a Chat Comes into Customer Service - and is then Transferred to Technical Support and then Advanced Support. This would be 1 Chat with ideally 3 engagements (one for each Dept). The Chat Department would end up as Advanced Support.

    I want to be able to run a report that counts the number of chats that initially landed in Customer Service (or Tech Support or Advanced Support)

    I initially tried trimming the ".1" from the engagementid suffix <ChatID>.1 for first engagement and using that to populate a new attribute using Engagement DEpt - but this is not reliable as if the first enagement is missed, then the engagement dept is null.

     

    Hopefully I have been overthinking this and there's a simple way to accomplish what I am trying to do, but I've so far come up against dead ends. Any help appreciated!

    0
  • Ben Fulton

    Hi Graeme,

    Thanks—it turns out that the change is reported as an underlying tag change rather than reporting the field value's human readable name, which is not optimal.

    But at least I now know what to look for. However, this is difficult enough to deal with that I'm starting to consider other methods to record this fact. 

    0
  • Ben Fulton

    Hi Graeme,

    One final question—what I really want to do is build a graph of the *count* of each max value, so I can illustrate what percentage of each week's tickets where maximally escalated to each group.

    Is it possible to build a metric that is a COUNT of a set of MAX values?

    0
  • Graeme Carmichael
    Community Moderator

    Ben

    Sorry, but I am not sure how to do that.

    There is a result manipulation>result metric calculation to perform additional calculations on your metrics, but you can only COUNT attributes not metrics.

    0
  • Jeff Foss

    How would I calculate if something previously existed in a state. For example: how many tickets were at one point tagged with a given tag, but may not necessarily have that tag still attached to them.

    0
  • Christopher Le Quesne

    Hi - why does the WEEKDAY_NUMERIC() function not allow us to set the parameter for when the week starts? Even Excel lets us do this, this should be fundamental to an analytics tool.

    0
  • Gab Guinto
    Zendesk Customer Care

    Hi Jeff,

    If the tags are associated with ticket field/filed values, then you should be able to you can track this by using the Changes attributes (Changes - Field name, - Previous value, and - New value) under the Ticket updates dataset. But, if what you are looking to track are just tags added to the tickets, then I'm afraid that is not possible at this time.

    Thanks!

    0
  • Gab Guinto
    Zendesk Customer Care

    Hi Christopher,

    I agree that this would be a very useful functionality if made available in Explore. Sorry if this weekday_numeric function falls a little bit short here.

    For now, I recommend that you create a new post in the Explore Product Feedback topic in our community to engage with other users who have similar needs and discuss possible workarounds. Conversations with a high level of engagement may also get flagged for future roadmap planning.

    Thanks!

    0
  • Reviewer

    I am running into issues trying to create custom metrics and excluding tickets that contain certain tags. My metrics work fine before testing for tags, and I have tried using "not contains" as an alternative. I am using d_count and the metrics otherwise works until the tag condition. 

    IF IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday","Saturday"))
    AND IN([Ticket created - Hour],ARRAY("7", "8", "9", "10", "11", "12","13","14","15"))
    AND (SUM(First reply time (min)) > 60)
    AND NOT INCLUDES_ANY ([Ticket tags], "no_stats", "closed_by_merge")
    THEN
    [Ticket ID]
    ENDIF

    Any tips?

     

    0
  • Graeme Carmichael
    Community Moderator

    David

    Sorry, but I cannot replicate your problem.

    Sometimes it is best to tick the 'Compute separately' option when creating a custom metric if you have other metrics in your query. Perhaps worth a shot? 

     

    0
  • Reviewer

    Graeme, you gave me the confidence that it should be working so I took it to another account and it worked fine there. I have submitted a support ticket to resolve. Thank you!

    0
  • Graeme Carmichael
    Community Moderator

    David

    Please let me know if I have missed something.

    Thank you!

    0
  • Madeline K.

    Hello! 

    I am trying to create a custom metric to calculate the percentage of two custom fields. One of the custom fields is multi-select, but the other is not. I am looking for a 1:1 ratio so count values does not work for me because some tickets may have multiple values selected which is skewing the results. Is there a way to calculate the number of tickets with a value in this custom field instead?

    0
  • Milton Lobo

    Not sure what's changed, but looks like the DATE_FORMAT function stopped working correctly in the last week.

    DATE_FORMAT(START_OF_MONTH([Date]),"YYYY-MM-dd")

    now returns a timestamp whenever day is included: 2021-05-01T00:00:00

    1
  • Nhia Lor

    Hi Madeline,

    Multi-select values are treated the same way as tags are in Explore so each multi select value within the field will appear as seperate attribute values when selected. As there can be multiple values that exist in your use case it might be best if you firstly created a seperate custom metric that could aggregate the ticket count as only one when any applicable multi select value is selected. You can use a simply custom metric like this example below to help you get started.

    IF ([Multi-select] = "multi select value 1" OR [Multi-select] = "multi select value 2")
    THEN [Ticket ID]
    ENDIF

    Once you created something like the above you should be all good to try and incorporate that custom metric in your original metric to perform the percentage calculation.

    Hope that helps!

    Best regards,
    Nhia.

    0
  • Nhia Lor

    Hi Milton,

    Thanks very much for bring this to our attention.

    We're currently working on this resolving this behaviour but I will create a ticket on your behalf so we can update separately once we have things all sorted!

    Best regards,
    Nhia.

    0
  • Trevor Kanaya

    Is it possible to use the DATE_GREATER(_date,_date) function to specify any tickets created after a specific date? If so, how would we format the custom date?

    For example, if I only want to see tickets after today, I want to do something like:

    DATE_GREATER([Ticket created - Date],"07/30/2021")

    or something to this effect. Obviously this doesn't work as I've tried it, as well as a few other date formatting possibilities without success. Is specifying a date possible?

    Cheers,

     

    0
  • Chandra Robrock
    Community Moderator
    Most Helpful - 2021

    @... Try using the following formula instead with a slightly different date format:

    DATE_GREATER([Ticket created - Date],"2021/07/30")

    0
  • Trevor Kanaya

    @... I think that worked, I'm seeing the number I'm more-or-less expecting - thanks! However, in order to verify, I added rows to the query for ticket ID and date created just to verify none were prior to the date I set. Now I can't get any data to render. The amount of tickets the query is showing is <200 so Explore shouldn't have any issue creating a table for that number of rows but it keeps telling me that the query took too long to execute. Could this have to do with something wrong with the metric or why else might it be trying to show so many rows when the number of results is so small?

     

    EDIT: Figured this out, it was my calculated metric, it had other conditions alongside the date one, so even though it was only returning <200 results, the amount of lines in the db being queried was >50,000. Initially this error was not being returned and I was just getting a blank area where the visualisation would normally render, so I didn't know what was going on. Cheers

    0
  • CJ Johnson

    What about more than and less than mathematical operators? Can I use them in conjunction with equal signs, or no? 

    0
  • Gab Guinto
    Zendesk Customer Care

    Hi CJ,

    Yes, you can use <= or >= in your metric formula. Example:
    ...IF VALUE(Agent replies)<=5...
    0
  • Dayana Flores

    Hi there!

    I need to build a query to get the total time spent by agent on tickets!

    For example:

    Ticket ID 1 is created

    TIER 1

    -Agent 1 receive the ticket, and he has it assigned during 1 day, the reassignee to Agent 2
    -Agent 2 has 3 days the ticket assigned, THEN solve it.

    I need to get that in TIER 1 group the ticket ID #1 was 4 days since creation upon solving. Of those 4 days was 1 day with Agent 1 and 3 days with Agent 2.

    I'm doing it like this in Tickets Updates Dataset but I think I'm doing it wrong:

     

    This is the standard calculated metric I created:

    IF ([Changes - Field name] = "group_id"
    AND [Changes - Previous value]!=NULL
    AND [Changes - New value]!="0")
    THEN (VALUE(Total time spent (sec)))/3600
    ENDIF

    I hope you can help me :)

    0
  • Florian W.

    Hi there,
    I'm looking for a function that forces explore to show the visible ticket values only and ignore any values that are not visible.
    Sry that's hard to explain for me what I mean by visible. I'll try to explain it.
    We use conditional fields function and different ticket forms.
    Example:
    An agent selects ticketform1 and selects a product from dropdown productcategory1 and safes the ticket.
    Then the case changes due to further information by the requester.
    The agent now selects ticketform2 and selects a product from dropdown productcategory2.
    The agent does not delete the other ticketinformation (ticketform1, productcategory1).

    Now I create a report that shows a list of ticket IDs and Product Categorys in a table.
    As we have different dropdownfields with different productcategory fields I use a formula to combine the values in the table.
    Formula:
    [productcategory1] + "" + [productcategory2]

    In general that works great BUT only if there is no double information in the ticket.
    With the double information in the ticket a table can look like that:

    How can I force the formula to ignore the value that's actually not visible in the ticket?
    I was not able to find a function for that.


    0
  • Stephan Ossowski
    Hi Florian,

    In order to check this further, I will create a ticket on your behalf. You'll receive an email shortly, so we can move on from there.

    Have a nice day further 👋️
    0
  • CJ Johnson

    It doesn't seem that "less than" operators actually function correctly. It'd be really helpful if the article covered these, if they are supposed to work, and provided example of the expected syntax. 

    0

Please sign in to leave a comment.

Powered by Zendesk