Skip to main content

Celonis Product Documentation

TRIMMED_MEAN
Description

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

The result of TRIMMED_MEAN is always a FLOAT column.

Lower and upper cutoff are rounded to the next smaller whole row number for each group (For example, trimmed mean over a group with 42 rows and a lower & 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 & lower rows cut is greater or equal to the count of the current group, trimmed mean will return 0 for this group.

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

  • upper_cutoff: INT between 0 and 100

NULL handling

NULL values are ignored, so they do not influence the result. If all the values of a group are NULL, the result for this group is also NULL.

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

[2]

Trimmed mean cuts off the given percentage of each group. Here the group for country "DE" contains 5 values. 50% of 5 is 2.5, so it is rounded to 2. This means we cut off the upper and lower 2 values and the result is the mean of the middle value which is 5. The same applies to "FR": 50% of 1 is 0.5, which is rounded to 0. For the country "US" we have four values. 50% is 2, so no rounding is needed and we cut off 4 values which leaves us with no value. The result for empty groups is then 0.

Query

Column1

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

Column2

         "Table1"."Country"
        

Input

Output

Table1

Country : string

Value : int

'DE'

10

'US'

2

'FR'

10

'DE'

3

'US'

40

'DE'

4

'DE'

22

'DE'

5

'US'

3

'US'

43

Result

Column1 : float

Column2 : string

5.0

'DE'

10.0

'FR'

0.0

'US'

See also: