You can create custom metrics and attributes to use in your report that help you present your business data just how you want it. To do this, you'll need to learn how to write formulas. Formulas perform comparisons, calculations, and manipulations on your data, and help you evaluate conditions.
For more information about formulas, seeFormula writing resources.
Understanding Explore formulas
Formulas are generally constructed with two pieces:
- Metrics and attributesthat contain your Zendesk information
- Functionsthat perform operations on those metrics and attributes
Formulas in Explore are read from left to right, and follow the mathematical order of operations. A formula can contain multiple statements, each containing a combination of metrics, attributes, and functions that are used to define the data you're looking for.
Within a formula, functions, such as IF/THEN, are often contained within parentheses(), much like algebra, so that they don't affect the rest of the formula. If a formula has only one function, parentheses might not be necessary.
Metric and attribute names are contained within brackets[]. Metric and attribute values, expressed as text strings, are contained in quotation marks, and must match the values exactly. For example, "End-user" and "end user" are two different values. (For more on attribute values in formulas, seeHow do I find an attribute value?) Operators are used to connect attribute names and values, or to perform additional calculations.
Putting this all together, a simple formula might read as follows:
Building a formula
Now that you know that an Explore formula is made up of metrics, attributes, and functions, you'll learn how to create the example formula above.
It can be helpful to write down the logic of a formula to help you create it and find the right functions you need. In this case, the formula flow is "If the ticket channel is email, show me the ticket ID." You'll see later that this is very similar to the formula you'll end up with.
To create a formula that displays tickets from the email channel
- In Explore,create a new reportoropen an existing report for editing.
- Open the calculations menu (
).Important:If two editors are creating or editing different reports in the same dataset, only the first person who opened a report can access the Calculations menu. The other editor will see the message “This resource is locked because it is currently being edited by”. Once the first person closes the report, the Calculations menu will become available to the next editor. - ClickStandard calculated attribute. A new blank attribute opens.
- Enter a name for the attribute likeTickets received by email.
- In the formula window, start entering your formula. There are a number of ways you can do this, but for now, under functions, clickAdd.
- Search forIF THEN ELSE Conditional expression. Either use the search box, clickFilterto scope the values down to theLogicalcategory, or scroll through the list until you find the function you want.
- Next toIF THEN ELSE Conditional expression, click+.
Explore adds a template IF THEN ELSE formula to the window. The values in the formula shown in blue (beginning with an _ character) are placeholders that you must replace with the values you need.
Tip:Notice the error message prefixed with (
) above the formula. This indicates a problem and its location in the formula. For now, you can ignore the error. It's appearing because your formula is still using placeholders. - Next, you'll replace_boolean_conditionwith the Explore attribute you want to test. Either highlight_boolean_conditionand then chooseTicket channelfrom theFieldsmenu, or amend the formula manually to add the attribute.
- Now, add the rest of the condition. To search for the string "Email", we add that to the existing condition.
Your formula should now readIF ([Ticket channel]="Email") THEN _value_if_true ELSE _value_if_false ENDIF
- Next, add what you want to do if the condition is true, in this case return the ID of the ticket. Replace_value_if_truewith[Ticket ID]. Notice that as you type[Ticket ID]探索自动建议匹配世界卫生大会t you are typing. If the metric or attribute you want is shown, hit return to immediately complete entry of it.
- The ELSE clause shown in the template ticket can be used to provide an alternative action that will apply if the first condition is not true. In this case, we don't need it, so removeELSE _value_if_false.
- You've now completed the formula. If all is well, you'll see something like the screen below. Additionally. note that the error has gone and is replaced with a green check mark to indicate that the formula is valid.
- Finally, clickSave. The attribute you created can now be accessed by any of your Explore reports that use the same dataset. You can find it by clickingAddin any attribute panel and expandingCalculated attributes.
Adding metrics and attributes to your formula
It's likely that most of the formulas you write with Explore will use metrics or attributes from the dataset you are currently using. In the example above you used the attributeTicket channel, but there are many hundreds more you can choose from.
While it can feel intimidating at first, most of these metrics and attributes have descriptive names that will help you to find what you need. Also, experiment with creating basic reports in Explore to learn more about what the various metrics and attributes do.
You can find a full list of all the metrics and attributes you can use in theUnderstanding Explore datasets.
To add a metric or attribute to your formula
- In the Explore formula window, clickSelect a field.
- 滚动列表,或输入first few letters of the metric or attribute you want, to filter the list.
- Click the metric or attribute you want and it is added to the formula. In this case, Ticket ID was chosen. Note how it is surrounded with brackets [].
The brackets indicate to Explore that this is a metric or attribute. An alternative method to enter a metric or attribute is to simply begin typing. Explore recognizes the brackets, and suggests the appropriate values to choose from.
如果你想添加一个聚合器度量或attribute, enter the text in capitals before the first bracket, for exampleCOUNT[Ticket ID].
Adding functions to your formula
To add functions to your formula, clickAddunderFunctionsor type in a function name. As with metrics and attributes, Explore suggests auto-complete results for any functions you type in.
When you clickAdd,Functionswindow opens. This window is useful if you are not sure which function is applicable to the calculated element you want to create. All functions in the window contain a brief explanation of their purpose. For a list of all functions and many examples, seeExplore functions reference.
If you are looking for a specific function, you can search for functions or filter by function type.
- In theFunctionswindow, clickFilter.
- Type in a category or select a category from the drop-down list.
- Click thexnext to the category name to remove the filter.
34 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
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:
The full list of text functions ishere.
Hi, I'm trying to replicate the following Insights custom metric in Explore and can't seem to get the formula correct. Any advice?
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.
HiErin Miller. I believe this custom metric will help. It's from the articleReporting with tags.
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?
HiErin 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 calculatedattributes.
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!
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
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?
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 thencreate a metric to check for the tags.
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?
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
Cheers!
HiZendesk 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!
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
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:
I feel like i need the metric first an the rest will flow into place.
Any input on this would be awesome!
Cheers
James
Do these tickets haveCustomer Onboarding selected as their ticket form? If so, then you can try this custom metric formula:
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.
Thanks@..., i have what i need!
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?
I will open up a ticket with you to take a look at your formula.
Best regards,
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:
As I am currently working with theGuide - 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)
BothResolution article ticketsandLinked article ticketsare 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!
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 toGuide Datasetfor the formula.
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.
Thanks for pointing this out,Judy Correia! I've updated thefirst set of steps in this articleto mention that you first need to create or open a report.
When a custom formula is updated, what is the expected behavior for every existing report with that formula in use?
HeyCJ 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!
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?
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.)
如果你看到不同的东西发生在你身上r experience, we can better help you with your specific issue once you message us directly.
Pleasesign in留下你的评论。