Metric aggregators define how the data queried by a metric is calculated. For example, the metricFull resolution time (days)defaults to showing the median (MED) of the time in days it took to resolve tickets, but you can change the aggregator to show the average time, the minimum time, the maximum time and more.
Use this article to learn about the different aggregators you can use, and how to use them.
Changing the metric aggregator
To change the metric aggregator
- In the report builder, add a metric to theMetricspanel.
- Click the metric you added.
- From the list, select a new aggregator.
The available aggregators will differ depending on the metric you choose. Use theMetric aggregators referenceto find out more about each aggregator.
- To save the new metric aggregator, clickApply。
Metric aggregators reference
Use this table to learn about the different aggregators and when to use them.
| Aggregator | Description | When to use |
|---|---|---|
| COUNT | Counts all values of an attribute. If the same value is recorded more than once, each occurrence is counted. |
Used for counting objects like tickets, calls, chats, users, organizations, etc. |
| D_COUNT (distinct count) |
Counts the number of different (unique) values. Values recorded more than once are counted only once. |
Used for distinct counting objects like tickets, calls, chats, users, organizations, etc. |
| SUM | Adds up all numeric values. |
Used for database counted, arithmetical operation and time duration metrics like Chat Messages, % Satisfaction Score, Resolution Time. |
| AVG (average) |
Calculates the arithmetic mean of numeric values. |
Used for time duration and database counted metrics like Resolution Time and Chat Messages. |
| MED (median) |
Identifies the numeric value that separates the higher half of the values from the lower half. |
Used for time duration and database counted metrics like Resolution Time and Chat Messages. |
| MIN (minimum) |
Identifies the lowest numeric value. |
Used for time duration and database counted metrics like Resolution Time and Chat Messages. |
| MAX (maximum) |
Identifies the highest numeric value. |
Used for time duration and database counted metrics like Resolution Time and Chat Messages. |
VARIANCE STD_DEV (standard deviation) |
Measures how far numeric values are spread out from their average value. If it is low the numeric values are close to the average. If it is high the numeric values are spread out. |
Used for time duration and database counted metrics like Resolution Time and Chat Messages. |
| VALUE | Returns the exact value of a metric without any additional manipulation |
Used only in the formula editor when creatingcalculated metrics or attributes。Can be applied only to database-level metrics, not to other calculated metrics. See theVALUEtopic below. |
Choosing the right metric aggregator
By default, metrics use the most relevant aggregator. In some situations, a different aggregator might better suit your reporting needs or you might need to decide which aggregator to use on a new calculated metric. Use the following sections to help you to decide which metric aggregator to use.
COUNT and D_COUNT
- COUNT:Counting all recorded values.
- DCOUNT:Counts only unique values.
In many cases, both aggregators will return the same results because the values they are counting are not repetitive. For example, if you count Ticket IDs in theTickets dataset, the results for both aggregators are equal, because the ID of each ticket appears only once in this dataset. If you do the same in theUpdates history dataset, the COUNT aggregator returns a much higher result, because Ticket ID is recorded for each update.
For more information, seeWhat is the difference between COUNT and D_COUNT?
SUM
This aggregator is often used for database counters and arithmetical operation metrics, but it also can be used for duration metrics, for example, to calculate the total time spent by agents on calls or chats.
The database counted metrics are computed outside of Explore and appear in Explore as ready to use metrics. They normally measure the number of specific events or objects like Agent Replies, Assignee Stations, Reopens, or Chat Messages.
The arithmetical operation metrics are calculations created in Explore that perform numerical operations with existing metrics. For example, SUM should be used for % Satisfaction Score metric, which is a division of Good Satisfaction Tickets by Rated Satisfaction Tickets.
AVG, MED, MIN, MAX, VARIANCE and STD_DEV
团队效率和性能分析了禅desk using time duration and database counted metrics aggregated using AVG, MED, MIN, MAX, VARIANCE and STD_DEV.
AVG and MED play a similar role in understanding the tendency of a metric. If the data you are comparing is mostly uniform then the AVG aggregator can be used. However, in many cases, Zendesk data has a high number of outliers, meaning that MED is the best option. SeeAverage vs Medianfor a closer look at these aggregators.
The MIN and MAX aggregators can be used to identify the highest and lowest metric outliers.
VARIANCE and STD_DEV are more advanced analytical aggregators and are rarely used, but they are helpful in measuring how uniform or spread out the metric values are. If you never used these aggregators start with STD_DEV. Standard deviation is expressed in the same units as average and median aggregators and can be easily compared with them.
VALUE
The VALUE aggregator is different from other aggregators in that it’s available only inside of formulas. This means that you can’t set this aggregator from theMetricspanel as described above, but you can use it in the formula editor when creating a calculated metric or attribute.
The VALUE aggregator returns the exact value of a metric without any additional manipulation. In other words, it returns exactly what’s stored in the database for the metric it’s being applied to.
For example, in the following formula, each value of theFirst reply time (min)metric is evaluated. If that value is less than 5 minutes, the formula returns TRUE. If that value is greater than 5 minutes, the formula returns FALSE.
IF VALUE(First reply time (min))<5 THEN TRUE ELSE FALSE END
Additionally, it’s important to understand that the VALUE aggregator is intended to be used inside of formulas as part of a calculated metric or attribute—but within a formula, it can be applied only to database-level metrics, not to other calculated metrics.
To see what this means, consider the example above alongside a new example:
IF VALUE(Comments)<5 THEN TRUE ELSE FALSE END
In the first example, VALUE is being applied to theFirst reply time (min)metric in theSupport: Ticketsdataset. Looking at theformula for that metric, you’ll see that it’s simply(First reply time (min))。Because there’s no calculation applied to that metric, it’s considered a database-level metric.
In the second example, VALUE is being applied to theCommentsmetric in theSupport: Updates historydataset. Theformula for that metricisIF ([Comment present] = TRUE) THEN [Update ID] ENDIF。因为额外的计算应用于那t metric, it’s considered a calculated metric, although one that’s built into Explore. This means that VALUE can’t be used with theCommentsmetric inside of a formula.
For more help, seeTroubleshooting errors in Explore formulas。
4 Comments
I'm working on moving our OKRs over to Explore from Insights (GoodData) and have hit a blocker. Does anyone have a good way to do this in Explore? I'm looking to recreated the following calculation for variance on MTTR. This was my attempt to move this to Explore.
The original query was:
Hi Nick! At this time, Explore does not have a PERCENTILE function within metrics. You can review available metric functions here:Explore functions reference。
The closest option is the "Percentile" pattern under Result Path Calculations. The details are here:Using result path calculations。
It's possible to get the values for the 75th and 25th percentiles in Explore. You can't manipulate them, like finding the difference or incorporating those numbers in larger charts. You can find and display them, though, which is a start.
To display those percentile values:
The result is the 75th and 25th percentiles, displayed with their resolution time and associated ticket IDs.
Here are screenshots from Insights and Explore in a test environment, so you can see the values line up. Explore is using the method above, while Insights is using SELECT PERCENTILE(Full resolution time in minutes, ##)/60 for its metrics.
Explore isn't as robust as Insights when it comes to percentiles (for now), but there is an option available.
I hope this helps! Happy reporting!
Hi all,
We are using a custom metric that saves the timestamp of every change of "Step" (step 1 to step 2 to step 3, etc.) for every ticket.
Now we want to obtain the Median but only of the MIN values of this metric, is that possible? The idea is to be able to calculate for every organization that we have the Median value of that first change of "Step".
Hi Team,
I want to track the pending status time during which a ticket had the custom value "issue_status_needs_investigation" assigned.
In your reference here, you say value aggregators canonlybe used within calculated metrics.
If I try to use the following formula (which I adjusted from yourhelp center article for field changes timeit tells me I can't use the aggregator VALUE in calculated metrics, which is exactly the opposite of what you say here above in this article
IF ([Changes - Field name]="Issue Status")
AND ([Changes - Previous value] = "issue_status_needs_investigation")
THEN VALUE(Pending status time (min))
ENDIF
The following formula, which is derived from your article, also uses a VALUE aggregator and is accepted.
IF ([Changes - Field name]="Issue Status")
AND ([Changes - Previous value] = "issue_status_needs_investigation")
THEN VALUE(Field changes time (min))/60
ENDIF
Can you explain or ideally, tell me how I could track what I want with a formula?
Pleasesign into leave a comment.