Recent searches


No recent searches

Explore functions reference



image avatar

Rob Stack

Zendesk Documentation Team

Edited Oct 02, 2024


3

53

53 comments

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


image avatar

ZZ 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


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


image avatar

ZZ 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


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


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


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


image avatar

ZZ 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


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


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


image avatar

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!

-2


image avatar

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


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


image avatar

ZZ 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


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


image avatar

ZZ Graeme Carmichael

Community Moderator

David

Please let me know if I have missed something.

Thank you!

0


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


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


image avatar

Nhia Lor

Zendesk Customer Care

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


image avatar

Nhia Lor

Zendesk Customer Care

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


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


image avatar

Chandra Robrock

Most Helpful - 2021Community Moderator

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

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

0


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

1


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

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi CJ,

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

-1


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


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


image avatar

Stephan Ossowski

Zendesk Customer Care

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


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


I'm seeing options in the editor that aren't listed here, like "VALUE" and "D_COUNT", which I'm assuming then are considered "aggregators", but they aren't listed on that page, either? https://support.zendesk.com/hc/en-us/articles/4408845551258-Working-with-aggregation-level-functions

Is there any reference guides for these parts of the formula? 

0


Please sign in to leave a comment.