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, see Formula writing resources.
Understanding Explore formulas
Formulas are generally constructed with two pieces:
- Metrics and attributes that contain your Zendesk information
- Functions that 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, see How 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:
Creating 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 report or open 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 <name>”. Once the first person closes the report, the Calculations menu will become available to the next editor. - Click Standard calculated attribute. A new blank attribute opens.
- Enter a name for the attribute like Tickets 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, click Add.
- Search for IF THEN ELSE Conditional expression. Either use the search box, click Filter to scope the values down to the Logical category, or scroll through the list until you find the function you want.
- Next to IF 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_condition with the Explore attribute you want to test. Either highlight _boolean_condition and then choose Ticket channel from the Fields menu, 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 read IF ([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_true with [Ticket ID]. Notice that as you type [Ticket ID] that Explore automatically suggests matches to what 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 remove ELSE _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, click Save. The attribute you created can now be accessed by any of your Explore reports that use the same dataset. You can find it by clicking Add in any attribute panel and expanding Calculated 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 attribute Ticket 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 the Understanding Explore datasets.
To add a metric or attribute to your formula
- In the Explore formula window, click Select a field.
- Either scroll through the list, or type the 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.
If you want to add an aggregator to your metric or attribute, enter the text in capitals before the first bracket, for example COUNT[Ticket ID].
Adding functions to your formula
To add functions to your formula, click Add under Functions or type in a function name. As with metrics and attributes, Explore suggests auto-complete results for any functions you type in.
When you click Add, the Functions window 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, see Explore functions reference.
If you are looking for a specific function, you can search for functions or filter by function type.
- In the Functions window, click Filter.
- Type in a category or select a category from the drop-down list.
- Click the x next to the category name to remove the filter.
Adding comments to your formula
You can add comments to your formulas to explain how it works. This is helpful if you want to collaborate on formulas with other users, or if you want to make notes for yourself on why you built the formula the way you did.
To add a comment in a formula
- Find the place in a formula where you want to insert a comment.
- Type a forward slash and an asterisk (/*), type your comment, and then type an asterisk
and a forward slash (*/).Warning: Clicking Format removes all comments. If you click Format and want to restore your comments, click anywhere outside the panel to close it without saving your changes.
47 comments
CJ Johnson
I can confirm the described behavior is not the experienced behavior. If you have a report saved on a Dashboard, and you change a formula that is an attribute on that report, the report will not reflect the changes to the attribute on the Dashboard, unless you edit and re-Publish the entire Dashboard.
0
vincent solitario
Hello, Zendesk team
We do have to 2 ticket brands in zendesk and I wanted to merge the total tickets created between the two.
I tried filtering two attributes but the result is not what i'm looking for.
In brand 1, we are using received thru attributes
In branch 2, they are using Channel attributes
As I add this attributes in filter or in Columns, Rows and even creating metric tickets to those 2 attributes, only the tickets created in Channel will show.
I tried creating formula but the formula is not working.
Is their a way to merge this or can you give the formula in this.
And may i know how to count the ticket Updated by other agent.
Ex.
Agent 1 created the ticket and agent 2 commented on the same ticket.
How ca we count or what kind of ticket fields we should use on the ticket commented by agent2?
0
Dane
As far as I know, you don't need to create any custom metrics for this. You will just have to add "Ticket Brand" under filters and select the brand you have and it will automatically show you the tickets for those brands.
0
Jacob the Moderator
Hi Amos Chen
That would be
You can find the reference to all Explore functions here.
0
Roshni Daftary
I am trying to split the Requester Name into First and Last Name with just the 1st space as the differentiation. Anything before the 1st space to be counted as 1st name and rest all as last name. I have tried leftpart / rightpart. It doesnt seem to be working. Any other inputs.
0
Zsa Trias
Hello Roshni,
This seem to be possible by combining multiple Explore Functions.
I was able to achieve separating first and last name using this formula:
For the first name:
For the last name:
1
Roshni Daftary
Thanks Zsa Trias.. this worked.
0
Bruce Willis
Hi,
I'm trying to create a statistic regarding the behaviour of users in a specific workflow, but I can't seem to combine the logic correctly.
I need to filter the users that created tickets sending an email to a specific address, and to create a chart showing the allocation of ALL OF THEIR OTHER TICKETS between all the forms when they were submitting tickets (ie: understand which form is generating more complaint emails).
Thanks!
0
Devika
Hi! How can you for example calculate the percentage of 1 particular topic compared to the total amount of tickets for example?
0
Dane
I think you can use the attributes ticket form, ticket email address. However, I'm not aware on how you want it to be shown on your query. Hence I cannot provide a recommendation on how to write the formula. If you can give me an overview, I'll try to create one for you.
0
Bruce Willis
Hi Dane,
thank you for your support.
I would need to create a pie chart of all of the tickets created by specific users showing the allocation between all the available forms on the account.
Those users should be defined by the fact that they created at least one ticket by sending an email to a specific email address let's call it EMAIL1. Please note that the pie chart should show all of the tickets created by those users, not restricted to only the tickets created via that specific email address (sent via EMAIL2, forms, answer bot etc).
Thank you very much!
Aaron
0
Dane
0
Thomas Lang
I am trying to create a formula that will track anytime someone changes the form field to something specific: Here is my formula, but I believe the changes value requires API, anyway to do this in explore wihout it?
IF ([Changes - Field name]] = "Ticket Form" AND [Changes - Previous value] != "Ticket Form 1" AND [Changes - New value] = "Ticket form 1") THEN [Update ticket ID] ENDIF
0
James G
I suggest creating a test report using "Sos Requester Region" as the only attribute so you could see the actual attribute values that would show up if it is really "NAR (US)" and "NAR (CA)". Once you confirm that it is really the case, try creating a simpler formula like this -
and see if it works properly. If not, feel free to send us a ticket so we can take a better look - Contacting Zendesk Customer Support.
0
Nikolay Atanasov - Support
Hey,
I am building a report that tracks how many accounts we closed over a set period of time. I've filtered it by ‘Assignee name/role’ and ‘Ticket channel’.
However, I'm struggling with filtering out tickets that have only internal comments. I want the results to show only tickets with public or both public and internal comments.
Is there a way to build such formula?
Thanks!
0
Zaffar Sayed
Hello,
I’m trying to create a custom attribute to capture tickets created in the week before the last (i.e., two weeks ago). Could someone please assist me with the formula for this?
0
Jerina Hall
Hi - I am trying to create an end user submitted tickets forecast but remove tickets from the results that contain specific tags. When I try to do it by creating standard calculated metrics IF (NOT INCLUDES_ANY([Ticket tags], "tag1")) THEN [Ticket ID] ENDIF what I end up with is lots of different lines but what I am trying to achieve is one line indicating the values each year exluding the specific tags. Can anyone advise? Many thanks, Jerina
0