Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4
Description
In general, only one aggregation is calculated for a query. With a global aggregation it is possible to calculate an additional aggregation function over all values of a column.
GLOBAL calculates an aggregation function for an aggregation to which all values are aggregated into one group.
Like normal aggregations, a global aggregation function take filters and selections into account. Values which are filtered out are not part of the result. Therefore if a filter or a selection is changed every global aggregation function is recalculated.
Syntax
GLOBAL ( AVG ( table.column ) )
GLOBAL ( COUNT ( table.column ) )
GLOBAL ( COUNT ( DISTINCT table.column ) )
GLOBAL ( MAX ( table.column ) )
GLOBAL ( MEDIAN ( table.column ) )
GLOBAL ( MIN ( table.column ) )
GLOBAL ( QUANTILE ( table.column, quantile ) )
GLOBAL ( STDEV ( table.column ) )
GLOBAL ( SUM ( table.column ) )
GLOBAL ( TRIMMED_MEAN ( table.column ) )
GLOBAL ( VAR ( table.column ) )
Note
The result of GLOBAL can not be used as a grouper column for an aggregation.
Examples
Calculating the sum for two groups and the count for one group
Query
Column1
"Table1"."Dim1"
Column2
SUM ( "Table1"."Column1" ) / GLOBAL ( COUNT ( "Table1"."Column1" ) )
Calculating the sum for two groups and the count for one group as a result of a filter
Query
Filter
FILTER "Table1"."Dim1" != 'C'
Column1
"Table1"."Dim1"
Column2
SUM ( "Table1"."Column1" ) / GLOBAL ( COUNT ( "Table1"."Column1") )