The group_aggregate function aggregates Measures at different granularities than the dimensions used in the search columns.Use the
group_aggregate function to aggregate Measures at different granularities than the dimensions used in the search columns.
How aggregation formulas work
Typically, the groupings and filters used in a formula use the same fields as columns returned in the search results — a grouping equates to an attribute column. For example, in the searchrevenue ship mode, revenue is the Measure and ship mode is the attribute, or grouping. The result shows total revenue for each ship mode.
Aggregation formulas in general are described in Aggregation formulas.
About flexible aggregation
Invoca provides flexible aggregation with thegroup_aggregate function, letting you group and filter query results on different dimensions and granularities than the columns already used in the search bar query.
group_aggregate uses a sub-query to perform these custom aggregations. If the sub-query is at a higher, less detailed level, Invoca adds the result column to the original query’s result. When the sub-query is at a finer detail level than the original query, Invoca uses roll-up, or reaggregation — particularly useful for comparison analysis.
To use groupings and filters, specify them with the query_groupings and query_filters keywords. You can also add or exclude groupings or filters.
Best practices for flexible aggregations
group_aggregate calculates a result at a specific aggregation level and returns it at a different aggregation level. For this reaggregation to return correctly, follow these guidelines:
- Wrap
group_aggregatein an aggregate function, such assumoraverage. - Make the wrapping function the immediate preceding function.
- Don’t use it with conditional operators.
Examples
For a search onrevenue monthly ship mode, add a formula to calculate yearly revenue by ship mode:
When group formula results are finer-grained than the search
Because groupings for group formulas are flexible, the computed column a formula creates can be finer or coarser grained than the search itself. When an additional aggregation is specified by the formula, the results get reaggregated.Reaggregation scenarios
Some scenarios require aggregation on an already-aggregated result. For example, computing minimum monthly sales per ship mode requires two aggregations: first a sum to compute total monthly sales per ship mode, then a min to compute the minimum sale for any given month for that ship mode.Groupings and filters
Flexible group aggregate formulas allow flexibility in both groupings and filters, giving you the ability to specify only groupings, or only filters.Where to go next
- For more examples, see the
group_aggregatefunction in the Formula function reference. - Aggregation formulas
- Group aggregation functions