Recent searches


No recent searches

Writing Explore formulas



image avatar

Rob Stack

Zendesk Documentation Team

Edited Jun 21, 2024


0

47

47 comments

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


image avatar

ZZ 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


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


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


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


image avatar

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


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


image avatar

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!

1


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


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


image avatar

ZZ 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


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


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


image avatar

Marco

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


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


image avatar

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

0


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


image avatar

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


Thanks @..., i have what i need!

0


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


image avatar

Alex Zheng

Zendesk Customer Care

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

-1


Hey everyone,

I am confused about when to use parentheses ( ) and when to use brackets [ ]. I'll explain the context first before getting to the example wherein my confusion lies.

In the paragraph on 'Understanding Explore formulas', it is explained very clearly:

  • Parentheses ( ) are used with functions, so that they do not affect other parts of the formula but are contained within themselves. They can be optional, should the function have only one formula.
  • Brackets [ ] are used to insert metrics and attributes. They appear to be conditional, meaning that you always must use brackets to insert them. This is not clearly stated though.

As I am currently working with the Guide - Knowledge Capture dataset, I am choosing an example here, but I have observed this in other datasets as well.

Here we have a metric called '% Ticket resolution rate'. It uses the formula:

D_COUNT(Resolution article tickets)/D_COUNT(Linked article tickets)

Both Resolution article tickets and Linked article tickets are metrics. They are calculated metrics that belong to the same dataset.

Why do these metrics not have to be inserted in brackets [ ] but are only in parentheses ( )?

Does this have something to do with whether or not the metric is a 'database counted metric'?

Any help will be appreciated! Thank you in advance!

0


image avatar

Dane

Zendesk Engineering

Hi Laura,
 
The "[]" denotes that these are the base metrics and attribute values. "Resolution article tickets" is already a combination of multiple attributes in a function. Therefore it will be shown with "()".
 
Please refer to Guide Dataset for the formula.
 
IF ([Knowledge Capture type]="Solved") THEN [Knowledge Capture event ID] ENDIF

0


image avatar

Judy Correia

Zendesk Luminary

For newbies, a step is missing. You need to be in an existing report or creating a new report to access the calculations. This menu isn't just available from Explore landing page. 

0


Thanks for this feedback, Judy!

-1


image avatar

Erin O'Callaghan

Zendesk Documentation Team

Thanks for pointing this out, Judy Correia! I've updated the first set of steps in this article to mention that you first need to create or open a report. 

0


When a custom formula is updated, what is the expected behavior for every existing report with that formula in use? 

0


image avatar

Jon Daniels

Zendesk Customer Care

Hey CJ Johnson!

When you use a formula to build a custom attribute or metric, it is expected that the attribute or metric works the same everywhere you use it, and will change in each report when the formula is changed.

If you don't see this happening, please message us - we'll be happy to assist!

0


Hi Jon, 
Can you clarify if that means that a filter that is changed, should stay checked with the existing options, if that filter's formula is changed? 

Can you clarify if Dashboards with reports on them, that contain formulas with updates, still need to be opened, have each tab with an affected report loaded, and have "publish" pushed for each one? 

0


image avatar

Jon Daniels

Zendesk Customer Care

[Updated my own comment in 2023 with a correction: my original comment is not how it works, every change that changes a report (formula etc) requires that report to be re-published on the dashboard (as outlined here: https://support.zendesk.com/hc/en-us/articles/4408823403546-Editing-reports#:~:text=After%20you%20create%20a%20report,a%20report%20at%20a%20time.)

The changes to any formula should be effectively shown in the values on each of the reports/dashboards using the metric or attribute that the formula is based on - the filters would only be affected if they were based on the formula (for example, a date filter wouldn't be affected at all)
(Quickly editing to make it clear that the reports and dashboards should show the new values as soon as the formula they are based on changes, since the formula is what determines the value.)

If you're seeing something different happen in your experience, we can better help you with your specific issue once you message us directly.

0


Please sign in to leave a comment.