Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

VAR

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

Description

VAR calculates the variance per group. Variance can be applied to INT or FLOAT columns.

Syntax
VAR ( table.column )
NULL handling

NULL values are ignored, meaning that they do not influence the result. If all values of a group are NULL, the result for this group is also NULL.

Examples

[1] Variance of column.

Query

Column1

VAR ( "Table"."Column" )

Input

Output

Table

Column : INT

1

2

3

Result

Column1 : FLOAT

1.0

[2] Get the variance by country.

Query

Column1

"Table"."Country"

Column2

VAR ( "Table"."Values" )

Input

Output

Table

Country : STRING

Values : INT

'US'

1

'DE'

4

'DE'

4

'FR'

5

'US'

2

'US'

3

Result

Column1 : STRING

Column2 : FLOAT

'DE'

0.0

'FR'

0.0

'US'

1.0

[3] Get the variance with nulls, grouped by country.

Query

Column1

"Table"."Country"

Column2

VAR ( "Table"."Values" )

Input

Output

Table

Country : STRING

Values : INT

'FR'

10

'FR'

null

'DE'

null

Result

Column1 : STRING

Column1 : STRING

'DE'

null

'FR'

0.0