Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

GLOBAL

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

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

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

Note

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

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

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.

Inout

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: