Aggregation-level functions let you specify which attributes slice the metrics in your report, giving you more control over your Explore reports.
Normally, when you add a metric and multiple attributes to a report, the metric is sliced by all of those attributes. You can’t prevent the attributes from slicing the metrics, nor can you add attributes that slice the metric in the background without being present in the report. With aggregation-level functions, however, you can specify exactly which attributes will slice your metrics.
This article contains the following topics:
Available aggregation-level functions
Explore features the following aggregation-level functions.
| Function | Description | Example |
|---|---|---|
| ATTRIBUTE_FIX(metric, attribute1, attribute2, ...) | Slices the metric by the attributes specified in the function only. Attributes added to the report (in theColumnsorRowspanel) will not slice the metric, but any filters applied will be taken into account. Can be used to keep a report’s results from being affected by specific attributes, which is especially useful for calculating percentages. |
In this example, the median first reply time is returned for each month of the year, regardless of the attributes added to the report. |
| ATTRIBUTE_ADD(metric, attribute1, attribute2, ...) | Extends the metric aggregation to the attributes specified in the functionin addition tothose added to the report. Attributes added to the report will slice the metric, and any filters applied will be taken into account. 用来计算指标的平均值或中位数s, pre-aggregated, based on attributes not present in the report. |
In this example, the median first reply time is returned for each month of year, before being aggregated by attributes from the report. |
Using the ATTRIBUTE_FIX function
The first example below shows how the ATTRIBUTE_FIX function works, and the second and third show potential use cases.
Find the total number of tickets per group
This example illustrates how the ATTRIBUTE_FIX function works. You’ll create a metric that returns the total number of tickets in each group without being sliced by the attributes from the report.
To find the total number of tickets per group
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedTickets fixed to groupwith the following formula:
ATTRIBUTE_FIX(COUNT(Tickets), [Ticket group]) - In theMetricspanel, add theTicketsand theTickets fixed to groupmetrics. Use theSUMaggregator for the second metric.
- In theRowspanel, add theTicket groupattribute. At this point, both metrics show the number of tickets in each group.
- In theRowspanel, add theTicket statusattribute. Now, theTicketsmetric is sliced by ticket status and group, but theTickets fixed to groupmetric ignores the ticket status and is sliced only by the ticket group.
Find percentage of tickets from the quarterly volume
In this example, you'll create a report that shows the percentage of tickets created each month from the total number of tickets created each quarter.
To find percentage of tickets from the quarterly volume
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamed% of quarterly volumewith the following formula:
COUNT(Tickets) / ATTRIBUTE_FIX(COUNT(Tickets), [Ticket created - Year], [Ticket created - Quarter]) - Open the calculated metric you just created, clickOptions>Edit display format, and select%in the first field.
- In theMetricspanel, add theTicketsand% of quarterly volumemetrics. Use theSUMaggregator for the second metric.
- In theRowspanel, add the following attributes:
- Ticket created - Year
- Ticket created - Quarter
- Ticket created - Month
Your report should look like the following:
Compare the yearly and overall full resolution time
In this example, you’ll create a chart that shows the average full resolution time for each year and compares it to the overall average resolution time.
To compare the yearly and overall full resolution time
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedFull resolution time (fixed)with the following formula:
ATTRIBUTE_FIX(AVG(Full resolution time (min))) - In theMetricspanel, addFull resolution time (min)andFull resolution time (fixed).
- In theColumnspanel, addTicket created - Year.
Using the ATTRIBUTE_ADD function
The first example below shows how the ATTRIBUTE_ADD function works, and the subsequent examples give you potential use cases.
Find the average number of tickets per group
This example illustrates how the ATTRIBUTE_ADD function works. You’ll create a metric that returns the average number of tickets per group without adding the groups attribute to the report.
To find the average number of tickets per group
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedTickets per groupwith the following formula:
ATTRIBUTE_ADD(COUNT(Tickets), [Ticket group]) - In theMetricspanel, add theTicketsmetric.
- In theRowspanel, add theTicket groupattribute.
- Open theResult manipulation(
) menu, selectTotals, and setGrand totals ontoRowswith theAVGaggregator. - In theMetricspanel, add theTickets per groupcalculated metric you created. Use theAVGaggregator. Both metrics in the report show the number of tickets in each group, and the average per group is shown at the bottom of the table.
However, in this example, you don’t want to display groups on the table, only the average number of tickets per group.
- In theRowspanel, remove theTicket groupattribute, and also remove the grand total result manipulation. TheTicketsmetric returns the total number of tickets in your account, but theTickets fixed to groupreturns the average number of tickets per group.
- (Optional) In theRowspanel, add any additional attributes you want to slice the metrics. For example, add theTicket statusattribute. Now, theTicketsmetric returns the total number of tickets in each status, but theTickets per groupreturns the average number of tickets per group in each status.
找到门票每月平均数和中位数each quarter or year
In this example, you'll create a report that shows the average and median tickets created per month.
To find the average and median tickets per month for each quarter or year
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedTickets per monthwith the following formula:
ATTRIBUTE_ADD(COUNT(Tickets), [Ticket created - Year], [Ticket created - Month]) - In theMetricspanel, add theTicketsandTickets per monthmetrics. UseAVGandMEDaggregators for the second metric. (To select multiple aggregators, expand the metric and select each aggregator you want to add.)
- In theRowspanel, add theTicket created - YearandTicket created - Quarterattributes. As a result, your report shows the average and median tickets per month for each quarter:
- (Optional) Remove theTickets created - Quarterattribute to see the average and median tickets per month for each year.
Find the highest average resolution time per assignee
In this example, you'll create a KPI that shows the highest average resolution time across all assignees.
To find the highest average resolution time per assignee
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedResolution time per assigneewith the following formula:
ATTRIBUTE_ADD(AVG(Full resolution time (days)), [Assignee ID]) - Open the calculated metric you just created, clickOptions>Edit display format.
- In the first field, selectCustom, and then setDecimal placeto1andSuffixtodays(with a space before the word).
- In theMetricspanel, add theResolution time per assigneemetric. Use theMAXaggregator.
- In theFilterspanel, add theTicket solved - Dateattribute and configure this to show tickets solved in the last 30 days.
Your report shows the highest average resolution time across all assignees over the past 30 days.
Find the average number of ticket comments per agent
In this example, you'll create a bar chart that shows the average number of comments submitted by agents per ticket.
To find the average number of ticket comments per agent
- Create a new reportin theSupport - Tickets数据集。
- Create a standard calculated metricnamedComments per ticketwith the following formula:
ATTRIBUTE_ADD(COUNT(Comments), [Update ticket ID]) - Open the calculated metric you just created, clickOptions>Edit display format.
- In the first field, selectCustom, and then setDecimal placeto1.
- In theMetricspanel, add theComments per ticketmetric. Use theAVGaggregator.
- In theFilterspanel, add the following attributes:
- Update - Date: Set this to show updates from the last week.
- Updater role: Set this to exclude end users.
- In theVisualization type(
) menu, selectBar.
The report shows the average number of ticket comments for each agent.
13 Comments
hi@...
Thanks for this. That looks interesting . Our agents keep asking how they can compare their Monthly Sat score against the average Sat score in last 12 months. So the query would have bars of Sat score for recent months and then a trend line with average Sat score for 2021. The agent then would be able to see how their current month's Sat score is trending against average. Feels like a simple functionality, but we can't get this working. Would these new features help to build that? Tried few variations but can't seem to get it working
Hi Vladimir,
We may need to look into this further so a separate ticket will be created on your behalf where we can continue to look into the this.
Cheers!
Question, what would be the formula to compare an agents Average Full Time Resolution to the Team's Average Full Time Resolution? In Hours.
Can you use Attribute_Fix?
If you're looking to display each agent's resolution time and the groups' averages then, yes, you can use attribute_fix to build a metric that will only be sliced by ticket group. But, if what you're trying to do is to compare individual resolution times with the entire team's (not the ticket/agent group's) average resolution time, then you might want to use a fixed calculated metric (Adding fixed calculated metrics).
Hey there -
I'm having a tough time getting the ATTRIBUTE_FIX function to load metrics consistently on queries in the Ticket Updates data set. I thought this could be useful to build a metric have a count of Tickets created or solved by month, while also being able to slice another metric like Handling Time by various attributes (like individual agents or groups of agents) so you could measure those against total volume per month on a time series. I'm getting time-outs if I go beyond like a day's worth of data using the "Update - Date" time attribute as a filter, but when I just look at that one day, it looks correct. Does the ATTRIBUTE_FIX function not really work as well for the Ticket Updates data set or is there something inherently wrong with my approach?
Attribute functions could be used in any dataset. Let mego ahead and create a separate ticket on your behalf so I can get more account-specific details to troubleshoot this issue. Please expect an email shortly with the ticket information.
Hey,
Thanks for this article first of all. The use case of ATTRIBUTE_FIX seems pretty clear to me. However, I am having some trouble understanding the purpose of the ATTRIBUTE_ADD. In theory it makes sense, but I am confused in regards to the last example: 'Display the maximum number of tickets received from one channel in a month without showing the channel names'.
My confusion is this:
The metric returns the maximum number of tickets received by ALL channels in a month. In this case, why should we not just use the default COUNT(tickets) metric?
However, if we want to return the maximum number of tickets received by ONE (and only one) channel, must we not add a filter to the metric, choosing just one channel?
This does not seem clear in the article. Thank you in advance for your help!
Laura,
HiLaura
That's a great question. As the article described, "This example illustrates using the ATTRIBUTE_ADD function which slices the metric you specify by all attributes in the report plus the one you specify."
As you can see, the ATTRIBUTE_ADD actually uses the COUNT(ticket) metric for the custom-calculated metric in the samples:
This is for scenarios just like in the article where you want to display results without needing to slice the data, So instead of adding COUNT (tickets) + the attribute for tickets created in a year, since you don't want to list down the years and want just an average, you'd create your own metric with the ATTRIBUTE_ADD.
Hope this clears things out! Let me know if you have any follow-up questions.
HelloDainne Lucena,
Thanks for your reply.
My question however still prevails.
In the example that you provide, the results of what year are being rendered? Wouldn't you have to specify which year?
It would be for all the years since the ATTRIBUTE_ADD returns the value ofaggregator(metric name) aggregated to all attributes in the report in addition toattribute1andattribute2.
HiEugene Orman, I need your expert help =)
I am using this formula to find the global average of Solved tickets in the whole Zendesk instance (it has just onesinglebrand). I can slice the reports with this metric by Dat Solved to have the average per any time period.
However, it doesn't work when there aremultiplebrands in Zendesk (because giving the average per brand). Is there really any attribute that is a global one for the whole Zendesk instance that hasmultiplebrands? Can't find anything and custom attributes don't seem to work here as well.
You can try to use the SUM aggregator instead of COUNT. I have tried to make a query and compared it to the actual Solved ticket per brand and it provided the same data.
DaneHey, thx for coming back. Seems like you missed my point. :) Let me explain another way.
I don't need to calculate this data per brand on the account. I need it per the whole accountwithmultiple brands. Do you see?
Pleasesign into leave a comment.