Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

MOVING_SUM

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

Description

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

Syntax
MOVING_SUM ( 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.

Examples

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

Query

Column1

"Table1"."MONTH"

Column2

MOVING_SUM ( "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 : INT

1

100

1

400

2

700

3

700

4

800

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

Query

Column1

"Table1"."MONTH"

Column2

MOVING_SUM ( "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 : INT

1

null

1

null

2

400

3

700

4

800

[3] Moving sum 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_SUM ( "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 : INT

2

400

4

700

See also: