Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

MOVING_TRIMMED_MEAN

Applies to: CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

Calculates the trimmed mean with a cut off of 10% for each window. It can be applied to INT or FLOAT columns.

Syntax
MOVING_TRIMMED_MEAN ( 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 trimmed mean for current row and one row above. Window is too small to do any trimming. Therefore the result is the same as for an average.

Query

Column1

"Table1"."MONTH"

Column2

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

Input

Output

MONTH : INT

INCOME : INT

1

100

1

300

2

400

3

300

4

500

Result

Column1 : INT

Column2 : FLOAT

1

100.0

1

200.0

2

350.0

3

350.0

4

400.0

[2] Moving trimmed mean for current row and one row above with null values. Window is too small to do any trimming. Therefore the result is the same as for an average.

Query

Column1

"Table1"."MONTH"

Column2

MOVING_TRIMMED_MEAN ( "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

400.0

3

350.0

4

400.0

[3] Moving trimmed mean for current row and one row above. Window is too small to do any trimming. Therefore the result is the same as for an average. 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_TRIMMED_MEAN ( "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

400.0

4

350.0

See also: