Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

RUNNING_TOTAL

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

Description

RUNNING_TOTAL sums up all entries of a given column and returns all intermediate sums. It can be applied to INT or FLOAT columns.

RUNNING_TOTAL sums up all entries of a given column, but instead of aggregating all values into a single total result, it returns all intermediate sums. RUNNING_TOTAL starts the summation at the top of the given input, respecting filtering and sorting.

Syntax
RUNNING_TOTAL ( table.column )
NULL handling

RUNNING_TOTAL returns NULL for all NULL values at the beginning of the column. After the first value that is not NULL was added to the sum, the current intermediate sum is returned on NULL values.

Tip

To specify sorting columns inside the function directly, RUNNING_SUM can be used. RUNNING_SUM also supports partitioning of the input.

Examples

[1] Simple example for Running Total.

Query

Column1

"Table1"."MONTH"

Column2

RUNNING_TOTAL ( "Table1"."INCOME" )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

100

1

200

2

300

3

400

4

500

Result

Column1 : INT

Column2 : INT

1

100

1

300

2

600

3

1000

4

1500

[2] Simple example for Running Total with null values.

Query

Column1

"Table1"."MONTH"

Column2

RUNNING_TOTAL ( "Table1"."INCOME" )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

null

1

null

2

300

3

null

4

500

Result

Column1 : INT

Column2 : INT

1

null

1

null

2

300

3

300

4

800

[3] Simple example for Running Total. A FILTER is applied, such that only rows with an INCOME value less than 400 are taken into account.

Query

Filter

FILTER "Table1"."INCOME" < 400;

Column1

"Table1"."MONTH"

Column2

RUNNING_TOTAL ( "Table1"."INCOME" )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

100

1

200

2

300

3

400

4

500

Result

Column1 : INT

Column2 : INT

1

100

1

300

2

600

[4] Running Total can also be performed on results of an aggregation.

Query

Column1

"Table1"."MONTH"

Column2

RUNNING_TOTAL ( AVG ( "Table1"."INCOME" ) )

Input

Output

Table1

MONTH : INT

INCOME : INT

1

100

1

200

2

300

3

400

4

500

Result

Column1 : INT

Column2 : FLOAT

1

150.0

2

450.0

3

850.0

4

1350.0

See also: