You may occasionally need to aggregate a value by a specific attribute, such as viewing total revenue by product. This is known as a grouped aggregation; some users also refer to it as a pinned measure or a level-based measure. You can perform these types of calculations for any aggregation using grouping functions.
Each of the grouping functions accepts a measure and one or more optional attributes using this format:
formula (measure, [attribute, attribute, ...])
Only the measure value is strictly required. If you provide both a measure and an attribute, the function returns the aggregate of that measure grouped by the specific attribute(s). We recommend experimenting with just a measure and then adding attributes to see which output best supports your specific use case.
group_* functions operate as their own sub-queries. They allow you to aggregate a column using groupings specified within the formula, which can differ from the groupings used in your primary search query.
For example:
group_max(balance date): This formula specifies no groupings. It acts like a sub-query for themax balance date, returning the very last balance date across the entire data set.group_max(balance date, balance date): This formula specifiesbalance dateas a grouping. It acts like a sub-query for[max balance date] [by balance date]. The bucketing used for grouping the data in this sub-query is inherited from your search, giving you the last balance date within every segment.
List of Group Functions
Group aggregation functions follow a standard group_<aggregation> format. The available functions include:
group_average: Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s).Example:
group_average (gifts, fund name)
group_count: Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s).Example:
group_count (gifts, customer region)
group_max: Takes a measure and one or more attributes. Returns the maximum value of the measure grouped by the attribute(s).Example:
group_max (gifts, fund name)
group_min: Takes a measure and one or more attributes. Returns the minimum value of the measure grouped by the attribute(s).Example:
group_min (gifts, fund name)
group_stddev: Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s).Example:
group_stddev (gifts, fund name)
group_sum: Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s).Example:
group_sum (gifts, fund name)
group_unique_count: Takes a column name and one or more attributes. Returns the number of unique values in a column, grouped by the attribute(s).Example:
group_unique_count (amount, payment type)
group_variance: Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s).Example:
group_variance (gifts, fund name)
Flexible Aggregation
The group_aggregate function provides even greater control over how your data is aggregated and filtered. To learn more about specifying query_groups within this formula, please see our Flexible Aggregation Functions article.
Limitations of Group Aggregation Functions
Please keep the following limitations in mind when using group aggregation functions:
You cannot create a KPI chart using a group aggregation function.
Trends AI does not support table aggregate headlines for formulas that utilize group aggregates and are conditional.
You cannot run a
vsquery that also includes a group aggregation function.You cannot run a group aggregation function on another group aggregation function. If you need to create a nested group aggregation, you can do so by saving the initial Chart (containing the first group function) as a View. You can then use that View as the data source for a second Chart to apply the next level of the group function.