How aggregation formulas work
Typically, the groupings and filters used in a formula use the same fields as columns returned in the search results. The concept of a grouping equates to an attribute column.
For example, in the search revenue ship mode
, revenue
is
the measure, and ship mode
is the attribute, or grouping. The result of this
search shows total revenue for each ship mode:
revenue | ship mode |
---|---|
$ a | air |
$ r | rail |
$ t | truck |
$ s | sea transport |
The aggregation formulas are described in Overview of aggregate formulas.
About flexible aggregation
ThoughtSpot provides flexible aggregation with the group_aggregate
function. You can use this formula
to group and filter query results on different dimensions and granularities from the columns already used in the search bar query.
The group_aggregate
formula uses a sub-query to perform these custom aggregations. If the sub-query
is at a higher detailed level, ThoughtSpot adds the result column to the result of original
query. When the sub-query is at a finer detail level than the original query, ThoughtSpot uses roll-up, or reaggregation.
This is particularly useful for comparison analysis.
To use the groups and filters, specify them using the query_groups
and
query_filters
keywords, respectively. You can also add or exclude groups or filters.
Best practices for flexible aggregations
The group_aggregate
function enables you to calculate a result at a specific aggregation level, and then returns it at a different aggregation level. For this reaggregation result to return correctly, follow these syntax guidelines:
-
Wrap
group_aggregate
in an aggregate function, such assum
oraverage
-
The wrapping function must be the immediate preceding function, such as
sum(group_aggregate(...))
-
Do not use with conditional operators. For example, the following expression does not reaggregate the data because the
if
precedesgroup_aggregate
:(if(group_aggregate(...)))
Examples
For a search on revenue monthly ship mode
, you can add a formula to calculate yearly revenue by ship mode:
group_aggregate(sum(revenue), {ship mode, year(commit date)}, {})
The same formula can also be written using query_groups()
and query_filters()
as following:
group_aggregate(sum(revenue), query_groups() - {commit date} + {year(commit date)}, {})
This is helpful to include the main query groups that are not known at formula
creation time. You can use +/-
to modify the set of groups included from
the query.
Finer-grained group formula results
The flexibility of groupings for group formulas enables you to create formula that generates a computed column that is finer or courser than the search itself.
For example, you can have a search that shows total yearly sales and a formula that computes total sales for each month (a finer-grained calculation than the search).
In such cases, if an additional aggregation is specified by the formula, the results get reaggregated.
Reaggregation can be applied in either of these ways:
-
You can add an aggregation keyword just before a formula column in a search. For example, in this search we’ve added the keyword min just before our formula for
monthly_sales
:sum revenue yearly min monthly_sales
where, the
monthly_sales
formula is written as:group_aggregate(sum(revenue), {start_of_month(date)}, {})
-
You can create a separate formula, such as in this search for:
sum revenue yearly min_monthly_sales
where, the
min_monthly_sales
formula is written as:min(monthly_sales)
Alternatively, if no aggregation is specified, then the search query also inherits the formula groupings, as in this search:
sum revenue yearly monthly_sales
where, the original query is computed at a monthly grain instead of yearly.
Reaggregation scenarios
Some scenarios require aggregation on an already aggregated result.
For example, computing minimum monthly sales per ship mode, requires two aggregations:
-
the first aggregation of sum to compute total monthly sales per ship mode.
-
the second aggregation of min to compute minimum sale that happened for any given month for that ship mode.
An example of this is this search:
ship mode min monthly_sales
where the formula monthly_sales
is written as:
group_aggregate(sum(revenue), query_groups() + {start_of_month(date)}, {})
For more extensive examples of using the group-aggregate
function, we encourage you to see Reaggreggation scenarios in practice
Groups and filters
Flexible group aggregate formulas allow for flexibility in both groupings and filters. The formulas give you the ability to specify only groupings or only filters.
Query filters
With query_filters()+{filter_condition}
or query_filters()-{filter_condition}
, users will be able to aggregate the results while including/excluding a filter condition.
Filter condition: Ship Mode='car'
For a search on Category Customer ID sales by customer id and category Ship Mode='car’
, you can add a formula to calculate sales by category for each customer as:
sales by Customer ID and Category= group_aggregate (sum(Sales), {Category, Customer ID }, query_filters()+{Ship Mode='air'})
In this case, the results will be aggregated based on the dimensions: ‘Category’
and ‘Customer ID’
and filters: ‘air’
and ‘car’
.
With query_filters()-{column}
, users will be able to aggregate the results while removing any expression related to a column.
Filter condition: Ship Mode='car'
For a search on Customer ID sales by customer id and category Ship Mode='car'
, you can add a formula to calculate sales for each customer while ignoring the filter on a column as:
sales by Customer ID and Category= group_aggregate (sum(Sales), {Customer ID, Category }, query_filters()-{Ship Mode})
In this case, the results will be aggregated based on the dimensions in the search; Customer ID and any filter related to Ship Mode will not be considered while aggregating the results.
Related information
-
For more examples of flexible aggregation, see the group_aggregate function in the Formula function reference.
-
To learn about aggregation formulas in general, see Overview of aggregate formulas and Group aggregation functions
-
To understand group aggregate query filters, see Aggregate filters
-
To learn about how the
group-aggregate
function can be used within your business practice, we encourage you to see Reaggregation scenarios in practice