Skip to main content

Celonis Product Documentation

GLOBAL
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 the 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 ( aggregation )
NULL handling

If the input value is NULL, then GLOBAL returns NULL as well.

Note

The result of GLOBAL cannot be used as a grouper column for an aggregation.

Like the standard aggregation functions, GLOBAL cannot be used inside a FILTER statement.

Use Cases
Examples

[1]

Calculating the sum for two groups and the count for one group

Query

Column1

         "Table1"."Dimension"
        

Column2

         SUM ( "Table1"."Value" ) / GLOBAL ( COUNT_TABLE ( "Table1" ) )
        

Input

Output

Table1

Dimension : string

Value : float

'A'

1.0

'B'

1.0

'A'

1.0

'B'

1.0

Result

Column1 : string

Column2 : float

'A'

0.5

'B'

0.5

[2]

Calculating the sum for two groups and the count for one group as a result of a filter

Query

Filter

         FILTER "Table1"."Dimension" != 'C';
        

Column1

         "Table1"."Dimension"
        

Column2

         SUM ( "Table1"."Value" ) / GLOBAL ( COUNT ( "Table1"."Value" ) )
        

Input

Output

Table1

Dimension : string

Value : float

'A'

1.0

'B'

1.0

'C'

1.0

'A'

1.0

'B'

1.0

'C'

1.0

Result

Column1 : string

Column2 : float

'A'

0.5

'B'

0.5

[3]

For each row, return 1 if the value is greater than the global average value (5) of that column:

Query

Column1

         "Table1"."Dimension"
        

Column2

         CASE WHEN "Table1"."Value" > GLOBAL ( AVG ( "Table1"."Value" ) ) THEN 1 ELSE 0 END
        

Input

Output

Table1

Dimension : string

Value : int

'A'

2

'B'

4

'C'

6

'D'

8

Result

Column1 : string

Column2 : int

'A'

0

'B'

0

'C'

1

'D'

1

See also: