Aggregation methods are types of calculations used to group attribute values into a metric for each dimension value. For example, for each country (each value of the Country dimension), you might want to retrieve the total value of transactions (the sum of the Sales Amount attribute).
On the component edit view, you select the aggregation method from a drop-down list. When using these aggregation methods in definitions of views and predefined metrics, you use the EQL syntax.
The available aggregation methods are:
Aggregation Method | Description | EQL Syntax |
---|---|---|
sum | Calculates the total value for the metric.
You can use this aggregation method for numbers and durations. You cannot use this aggregation method for multi-value attributes. |
sum(attributeName) |
average | Calculates the average value for the
metric.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
avg(attributeName) |
median | Calculates the median value for the metric.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
median(attributeName) |
minimum | Selects the minimum value for the metric.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
min(attributeName) |
maximum | Selects the maximum value for the metric.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
max(attributeName) |
variance | Calculates the variance (square of the
standard deviation) for the metric values.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
variance(attributeName) |
standard deviation | Calculates the standard deviation for the
metric values.
You can use this aggregation method for numbers, dates, times, and durations. You cannot use this aggregation method for multi-value attributes. |
stddev(attributeName) |
set | Instead of performing a calculation,
creates a list of all of the unique values.
You can use this aggregation method for any attribute. |
set(attributeName)
For multi-value attributes, use set_unions(attributeName). |
arb | Instead of performing a calculation,
arbitrarily selects one of the values.
You can use this aggregation method for any attribute. The arb aggregation method is typically not provided as an option for aggregating metric values on components. |
arb(attributeName) |
When configuring components, the following count aggregation options are applied using separate system metrics.
For the other aggregation methods, such as sum and average, you select an attribute, and then apply the aggregation method. For the count aggregation methods, you first select the system metric, then select the attribute to use.
When using these aggregation methods in definitions of views and predefined metrics, you use the EQL syntax.
Aggregation Method | Description | EQL Syntax |
---|---|---|
Number of records with values | Calculates the number of records with a
value for the selected attribute.
You can use this aggregation method for all data types. You apply this aggregation by adding the Number of records with values system metric to the component. |
count(attributeName) |
Number of unique values | Calculates the number of unique values for
the metric.
You can use this aggregation method for all data types. You apply this aggregation by adding the Number of unique values system metric to the component. |
countdistinct(attributeName)
Note that for multi-value attributes, countdistinct calculates the number of unique combinations of values, not the number of unique individual values. To get the number of unique individual values for a multi-value attribute, the syntax is cardinality(set_unions(attributeName)). This is the syntax used when you add the Number of unique values system metric to a component and select a multi-value attribute. For example, if the data contains three records, which have
following values for the
ItemColors attribute:
Then:
|
To show how each of the aggregation methods work, we'll use the following data:
Country | Amount of Sale | Shipping Company |
---|---|---|
US | 125.00 | UPS |
US | 50.00 | UPS |
US | 150.00 | FedEx |
Based on these values, the aggregation for Amount of Sale and Shipping Company for the US would be:
Aggregation Method | Amount of Sale (US) | Shipping Company (US) |
---|---|---|
sum | 325.00 | Cannot be aggregated |
avg | 108.33 | Cannot be aggregated |
median | 125.00 | Cannot be aggregated |
min | 50.00 | Cannot be aggregated |
max | 150.00 | Cannot be aggregated |
variance | 2708.33 | Cannot be aggregated |
standard deviation | 52.04 | Cannot be aggregated |
set | 125.00, 50.00, 150.00 | UPS, FedEx |
arb | Could be either 125.00, 50.00, or 150.00 | Could be either UPS or FedEx |
Number of records with values | 3 | 3 |
Number of unique values | 3 | 2 |