Group aggregation functions
What if you want to aggregate a value by a specific attribute (for example, show revenue by product)? This is known as a grouped aggregation, but some people call it a pinned measure or level-based measure. You can do this for any aggregation using the grouping functions.
Each of the grouping functions accepts a measure and one or more optional attributes:
formula (measure, [attribute, attribute, ...])
Only the measure value is required. If you supply both a measure and an attribute, the function returns the aggregate of the measure grouped by the attribute(s). You should experiment with only a measure and then with an attribute to see which output best meets your use case.
List of group functions
Group aggregation functions have names with formats like group_<aggregation>
.
The group aggregation functions are the following:
Function | Description |
---|---|
|
Takes a measure and optional attributes and filters. Used to aggregate measures with different granularities and filters than the columns used in the search. |
|
Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the maximum of the measure grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the minimum of the measure grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s).
|
|
Takes a column name and one or more attributes. Returns the number of unique values in a column, grouped by the attribute(s).
|
|
Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s).
|
Flexible aggregation
The group_aggregate
function gives you more control over aggregation and filtering.
See Flexible aggregation to learn more about specifying query_groups
with this formula.
Limitations of group aggregation functions
Group aggregation functions have the following limitations:
-
You can’t run SpotIQ analysis on a visualization that contains a group aggregation function.
-
You can’t create a KPI chart with a group aggregation function.
-
ThoughtSpot doesn’t support table aggregate headlines for formulas that have group aggregates and are conditional.
-
You can’t run a
vs
query that also contains a group aggregation function. -
You can’t run a group aggregation function on a group aggregation function.