Aggregation methods and the data types that can use them

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:
  1. Red, Blue
  2. Red, Blue, White
  3. Red, Blue
Then:
  • countDistinct(ItemColors) is 2, because there are two unique combinations of colors.
  • cardinality(set_unions(ItemColors)) is 3, because there are three unique colors.

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