In this article, you'll use what you learned in Creating standard calculated metrics and attributes to practice creating a standard calculated attribute by working through an example.
This article contains the following sections:
Step 1: Determine what you want to report on
In this example, you want to create a report that shows the following information:
- The median first reply time, in minutes, for the Support tickets in your Zendesk account
- Which channel the tickets were created in
After consulting Metrics and attributes for Zendesk Support, you know that you can use the First reply time (min) metric and the Ticket channel attribute to create your report.
Step 2: Start creating your report
Now that you know what information you're looking for, you can start creating your report.
To start creating your report
- In Explore, click the Reports icon (
).
- Click the New report button.
- On the Select a dataset page, select the Support - Tickets dataset and click Start report. The report builder opens.
- In the Metrics panel, click Add and select Duration between events - Calendar hours (min) > First reply time (min).
- In the Rows panel, click Add and select Ticket > Ticket channel.
You'll end up with a chart that looks something like this:
Step 3: Create a standard calculated attribute
Thanks to your report, you know that some channels have a much faster median first reply time than others. Now you want to know how the first reply time correlates to the ticket volume for each channel. You decide to update your report to answer the following questions:
- How many tickets are being created in each channel?
- For each channel, how many tickets have a fast first reply time, and how many have a slow first reply time?
Because there's no default Explore attribute for "fast first reply time" or "slow first reply time," you'll need to create a standard calculated attribute to define these concepts and reflect them in your report. You want to create a standard calculated attribute with the following logic:
- If a ticket’s first reply time is less than 10 minutes, group it under "Fast response"
- If a ticket’s first reply time is greater than or equal to 10 minutes, group it under "Slow response"
After consulting Explore functions reference, you decide that the best function to use in your formula is IF THEN ELSE. This function allows you to evaluate a condition (the first reply time) and then take an action based on the outcome of that evaluation (labeling the response fast or slow). The following formula is an efficient way to return the results you're looking for:
IF VALUE(First reply time (min)) != NULL AND VALUE(First reply time (min)) >= 10 THEN "Slow response"
ELIF VALUE(First reply time (min)) != NULL THEN "Fast response"
ENDIF
Now that you have your formula, it's time to create your standard calculated attribute.
To create the standard calculated attribute
- In your report, click the Calculations menu (
) and select Standard calculated attribute.
- In the Name field, enter a descriptive name like Response time fast or slow? You'll need this name later when you add your calculated attribute to the report.
- In the Formula field, paste in the
formula:
IF VALUE(First reply time (min)) != NULL AND VALUE(First reply time (min)) >= 10 THEN "Slow response" ELIF VALUE(First reply time (min)) != NULL THEN "Fast response" ENDIF
- In the Computed from dropdown, choose Ticket channel. This ensures that tickets are counted based on the Ticket channel attribute only.
- Click Save to save the attribute and close the Standard calculated attribute panel.
Now that you've created a standard calculated attribute, it acts just like any of the default attributes. It's available to use in any reports within the same dataset, and can be used by anyone who has access to that dataset.
Step 4: Add your attribute to your report
Next, you'll use your new attribute by adding it to your report. You’ll also add a new metric to count the number of tickets.
To add a metric and your standard calculated attribute to your report
- In the Metrics panel, click Add and select Tickets > Tickets, and then click Apply.
- Click the Tickets metric you just added and change its aggregator to D_COUNT.
- In the Rows panel, click Add.
- From the list of attributes, select Calculated attributes > Response time fast or slow? (the attribute you created above) and click Apply.
- To filter out tickets with no replies (the blank rows), select the Result
manipulation menu (
), click Metric filter, select Remove blank values under MED(First reply time (min)), and click Apply.
Your report now shows, for each channel, how many tickets had a fast response (first reply within 10 minutes) or a slow response (first reply after 10 minutes).
Note that the First reply time (min) column values have changed from the first version of your report. Because you added your calculated attribute to the report, the First reply time (min) metric is now being sliced by that attribute in addition to the Ticket channel attribute. In other words, instead of showing the median first reply time for all tickets in a given channel, the metric now shows the median first reply time for tickets in the “Fast response” and “Slow response” groups for each channel.
Step 5: Edit your attribute's formula
- If a ticket’s first reply time is less than 10 minutes, group it under "Fast response” (same as before)
- If a ticket’s median first reply time is greater than or equal to 10 minutes but less than 30 minutes, group it under "Slow response"
- If a ticket’s first reply time is greater than or equal to 30 minutes, group it under "Very slow response"
To update your attribute, you'll nest multiple IF THEN ELSE functions to evalute the extra condition. However, the basic structure of the formula is similar to what you've already created.
To edit the formula
- In the Rows panel, click the Response time fast or slow? attribute.
- Click the pencil icon below the attribute's name.
The Standard calculated attribute panel opens, showing your standard calculated attribute. - Replace the formula with the following:
IF VALUE(First reply time (min)) != NULL AND VALUE(First reply time (min)) >= 30 THEN "Very slow response" ELIF VALUE(First reply time (min)) != NULL AND VALUE(First reply time (min)) >= 10 AND VALUE(First reply time (min)) < 30 THEN "Slow response" ELIF VALUE(First reply time (min)) != NULL AND VALUE(First reply time (min)) < 10 THEN "Fast response" ENDIF
This is a useful way to avoid having to write multiple IF THEN ELSE functions. You could also use the SWITCH function to achieve a similar result.
- Click Save.
Explore automatically recalculates your report and displays the results, which now include the "Very slow response" group.
Next steps
Explore has hundreds of default metrics and attributes and a wide range of functions you can use in your formulas. You're encouraged to experiment with creating your own calculated metrics and attributes. The following articles are a great reference to help you:
15 comments
Hutton, Spencer
I have dozens of calculated metrics and attributes. Is there a way to group these in a way that makes managing them easier? For example, all of my calculated metrics are visible when I click Add then expand the group "Calculated Metrics". Is there a way to have my own groupings to organize my calculated metrics and attributes better as opposed to having them all in one large grouping?
1
Rob Stack
Hi Spencer, there isn't currently a way of doing what you need, but I agree that having a way of grouping custom metrics and attributes is a great idea. This would be a good request for our feedback forum at https://support.zendesk.com/hc/en-us/community/topics/360001200913 where it will get visibility from the product team and other customers can vote on it. Thanks!
0
Hutton, Spencer
Thanks Rob. And thanks for all you do for the community.
1
Rudy
If in following this example, you accidentally created a Standard Calculated Attribute instead of a Standard Calculated Metric, how do you delete it?
0
Philipp
0
Matt Farrington-Smith
Totally agree with Rudy here - I don't see why we still don't have better management for attributes/calculated metrics. It would be easy no?
I've just had an instance where the attribute I created is no longer hyperlinked in the metric - so I actually have no way of getting to it.
0
Brandon Frei
In the section "before you start" there are two links to different articles that are recommended. They both link to the same article. The "Understanding Reports" link is the one that is incorrect.
0
Erin O'Callaghan
Thanks for reporting this, Brandon Frei! I've fixed the Understanding reports link now.
0
Andrews Dumith
Greetings guys,
Thank you very much for the space to share knowledge and help us understand how this platform works so helpful for us resource managers.
I have been building a ticket database and have used several very beneficial metrics to measure the effectiveness of my team, in fact I only need one to complete the project and build my own graphs.
The metric or attribute that I am missing is the age of the tickets, so searching I found this formula:
IF ([Ticket custom status name] != "Solved")
THEN DATE_DIFF(NOW(), [Ticket created - Timestamp], "nb_of_days")
ENDIF
Which I have pasted to my report but for some reason I don't know, no values are shown in the report and I don't understand why. Possibly one of you has the key to the missing link :)
Thank you so much,
0
James G
Since your goal is to measure the number of days the ticket is unsolved, I would recommend using the default metric "Unsolved tickets age (days)" instead. This metric should give you the data that you are looking for based on your use case. You may find the complete definition of that metric in our list of metrics here - Tickets metrics.
0
Çağatay Kater
Can custom metric or attributes be used in Views?
0
Noly Maron Unson
Hi Çağatay,
Custom metrics and attributes can only be used in Explore reporting and are not present as conditions in Views. You can check the list of usable conditions for views in the article Creating views to build customized lists of tickets.
I hope this helps.
0
Joonas Niilerpalo
Hello!
I'm trying to create a metric:
Tickets created within 1 month of purchase, 2 months of purchase etc. I'm having trouble with the metric:
IF [Ticket created] - [Purchase Date] <=30 THEN [Ticket] ENDIF
Cant figure out, how the date difference should be written.
0
Alex Zheng
You'll want to use the date_diff function as described in the article here. So you can use DATE_DIFF([Ticket created - date],[Purchase Date],"nb_of_days") in your formula instead.
0
Annaya Thammalokapitak
Any idea how to create calculate metrics in Beta builder?
0