Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

MOVING_VAR

Applies to: CELONIS 4.5 CELONIS 4.6

Description

Calculates the variance for each window. It can be applied to INT or FLOAT columns.

Syntax
MOVING_VAR ( table.column, start, end )
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.

Tips

A group with only one non-NULL value will give the result NULL, since variance of one value only is undefined.

Examples

[1] Moving variance for current row and one row above.

Query

Column1

"TABLE1"."MONTH"

Column2

MOVING_VAR ( "TABLE1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

100

1

300

2

400

3

300

4

500

Result

Column1 : INT

Column2 : FLOAT

1

null

1

20000.0

2

5000.0

3

5000.0

4

20000.0

[2] Moving variance for current row and one row above with null values.

Query

Column1

"Table1"."MONTH"

Column2

MOVING_VAR ( "Table1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

null

1

null

2

400

3

300

4

500

Result

Column1 : INT

Column2 : FLOAT

1

null

1

null

2

null

3

5000.0

4

20000.0

[3] Moving variance for current row and one row above. A FILTER is applied, such that only rows with an INCOME value less than 500 are taken into account.

Query

Filter

FILTER "Table1"."INCOME" < 500;

Column1

"Table1"."MONTH"

Column2

MOVING_VAR ( "Table1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

null

1

null

2

400

3

500

4

300

Result

Column1 : INT

Column2 : FLOAT

2

null

4

5000.0