You can create custom metrics and attributes to use in your query and 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 calculations and manipulations on your data, and help you evaluate conditions.
Formulas are generally constructed with metrics and attributes that contain your Zendesk information and functions that perform operations on that information.
In this article, you'll learn the basics about writing formulas and see some examples of the power they can bring to your business analytics.
Building a formula
You previously learned that a typical Explore formula is made up of metrics or attributes and functions. In this section, you'll jump right in and learn the process by creating a formula.
For this example, you'll create a formula that displays only the tickets that were received through the email channel.
To create a formula that displays tickets from the email channel
- In Explore, open the calculations menu (
).
- 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 queries 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 Metric and attribute reference article.
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.
10 Comments
How do you use Attribute values?
For example I want to filter a certain value of an attribute (filter out deleted tickets), in GoodData it looked like this: "Ticket Status <> Deleted"
Hey Quentin!
Deleted tickets are already excluded from the Support Tickets dataset so any metrics you make there do not need any additional modifications. If you use the Ticket Updates dataset, deletion events will be included there.
Thanks Molly for the explanation.
But how do you filter by status for example? If I want to see only open tickets in my metric? Something like [Ticket Status]=Open?
Hi Quentin, there are two options.
1. Without a custom metric. Add the Ticket Status attribute to a panel and then click it. You can include, or exclude the values you want. In your case, you’d select only Open.
2. Use a custom attribute. You are correct, in your suggestion, but you do need quotes, for example:
[Ticket Status]="Open"
Hope this helps!
Perfect Rob!
Thanks for the tip :)
This article is linked to from here: https://explore.zendesk.com/hc/en-us/articles/235948948-Creating-standard-calculated-metrics-and-attributes as a reference for all available functions, but there's no list here of those functions?
Hi @Celia Johnson,
It seems you're correct. I assume you are referring to the link called Formula Writing Resources? I selected that and see what you mean, there is no explicit list. After following the link to Formula Writing Resources I noticed the same, no list of functions.
To assist in clarity of communication I have flagged these articles to check the hierarchy is set up as expected and to clarify where needed. For the time being I do think the formula writing resources will be helpful.
Thanks for raising this!
Hi Celia, I wanted to provide an update to your post above. We now link to a new article called "Explore functions reference" which does indeed provide a list of all available functions and help using them.
I do hope this helps and thanks for the valuable feedback.
How can I create a formula to show the end-user phone number in a Query? Currently, there is a row value for End-user email, but not for end-user phone number.
Paul
Unfortunately the user's phone number is not part of the dataset so you cannot report on it using Explore.
In Support, you would have to copy the phone number into a custom user field and then use the custom field in Explore.
Please sign in to leave a comment.