Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

TRIMMED_MEAN

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

Description

This function calculates the trimmed mean over an INT or FLOAT column.

The result of TRIMMED_MEAN is always a FLOAT column (Since: CELONIS 4.5).

Lower and upper cutoff get rounded to the next smaller whole row number for each group (e.g. Trimmed mean over a group with 42 rows and a lower and upper cutoff of 10% will result in the cut of the upper & lower 4 rows). By default 5% of the lower and upper values are cut off.

If the of upper and lower rows cut is greater or equal to the count of the current group, trimmed mean will return 0 for this group.

Null values are ignored, meaning that they don't influence the result. If all values of a group are null, the result for this group is also null.

Syntax
TRIMMED_MEAN ( table.column [, lower_cutoff [, upper_cutoff ] ] )
  • lower_cutoff: INT between 0 and 100

  • upper_cutoff: INT between 0 and 100

Example

[1] The values equals or above 100 and equals or below -100 are cut off. The mean is calculated over the values 1, 2, 3 and 4.

Query

Column1

TRIMMED_MEAN ( "Table1"."Value" , 30 , 30 )

Input

Output

Table1

Value : INT

102

101

100

4

3

2

1

-100

-101

-102

Result

Column1 : FLOAT

2.5