Skip to main content

Celonis Product Documentation

PU_TRIMMED_MEAN
Description

Calculates the trimmed mean of the specified source column for each element in the given target table.

Like the regular TRIMMED_MEAN operator, the column can either be an INT or FLOAT column. The data type of the result is always a FLOAT column.

Lower and upper cutoff get rounded to the next smaller whole row number for the specified source column (e.g. Trimmed mean over a source column 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 number of cut upper & lower rows is greater or equal to the number of rows in the source column, trimmed mean returns 0.

The values of the specified source column are sorted in descending order before the cutoffs are applied.

Syntax
 PU_TRIMMED_MEAN ( target_table, source_table.column [, lower_cutoff [, upper_cutoff ] ] [, filter_expression ] )
  • target_table: The table to which the aggregation result should be pulled. This can be:

  • source_table.column: The column which should be aggregated for every row of the target_table.

  • lower_cutoff (optional): INT between 0 and 100.

  • upper_cutoff (optional): INT between 0 and 100.

  • filter_expression (optional): An optional filter expression to specify which values of the source_table.column should be taken into account for the aggregation.

NULL handling

If no value in the source table column exists for the element in the target table (either because all values of the source table are filtered out, or because no corresponding value exists in the first place), NULL will be returned. NULL values in the source table column are treated as if the row does not exist.

Examples

[1]

A simple example that mimics the example shown for the standard TRIMMED_MEAN operator. 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

         PU_TRIMMED_MEAN ( CONSTANT ( ) , "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]

Calculate the trimmed mean of the case table values for each company code with the lower cutoff & upper cutoff set to 20%:

Query

Column1

         "companyDetail"."companyCode"
        

Column2

         PU_TRIMMED_MEAN ( "companyDetail" , "caseTable"."value" , 20 , 20 )
        

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

null

4

'001'

500

5

'001'

200

6

'001'

300

7

'001'

null

8

'001'

600

9

'002'

300

10

'002'

300

11

'002'

500

12

'002'

100

13

'002'

null

14

'002'

400

15

'002'

500

16

'003'

200

17

'003'

400

18

'003'

100

19

'003'

100

20

'003'

null

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : string

Column2 : float

'001'

450.0

'002'

375.0

'003'

200.0

[3]

PU-functions can be used in a FILTER. In this example, the company codes are filtered such that the corresponding trimmed mean with an upper cutoff of 20% of the case table values is smaller than 300:

Query

Filter

         FILTER PU_TRIMMED_MEAN ( "companyDetail" , "caseTable"."value" , 0 , 20 ) < 300;
        

Column1

         "companyDetail"."companyCode"
        

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

null

4

'001'

500

5

'001'

200

6

'001'

300

7

'001'

null

8

'001'

600

9

'002'

300

10

'002'

300

11

'002'

500

12

'002'

100

13

'002'

null

14

'002'

400

15

'002'

500

16

'003'

200

17

'003'

400

18

'003'

100

19

'003'

100

20

'003'

null

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : string

'003'

[4]

PU-functions can be used inside another aggregation function. In this example, the maximum value of all trimmed means with an upper cutoff of 20% of the case table values for each company code is calculated:

Query

Column1

         MAX ( PU_TRIMMED_MEAN ( "companyDetail" , "caseTable"."value" , 0 , 20 ) )
        

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

null

4

'001'

500

5

'001'

200

6

'001'

300

7

'001'

null

8

'001'

600

9

'002'

300

10

'002'

300

11

'002'

500

12

'002'

100

13

'002'

null

14

'002'

400

15

'002'

500

16

'003'

200

17

'003'

400

18

'003'

100

19

'003'

100

20

'003'

null

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : float

400.0

[5]

Calculate the trimmed mean with a lower & upper cutoff of 20% of the case table values for each company code. Only consider cases with an ID smaller or equal to 10. In this example, NULL is returned for companyCode '003' because the number of cases after filtering is 0:

Query

Column1

         "companyDetail"."companyCode"
        

Column2

         PU_TRIMMED_MEAN ( "companyDetail" , "caseTable"."value" , 20 , 20 , "caseTable"."caseID" <= 10 )
        

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

null

4

'001'

500

5

'001'

200

6

'001'

300

7

'001'

null

8

'001'

600

9

'002'

300

10

'002'

300

11

'002'

500

12

'002'

100

13

'002'

null

14

'002'

400

15

'002'

500

16

'003'

200

17

'003'

400

18

'003'

100

19

'003'

100

20

'003'

null

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : string

Column2 : float

'001'

450.0

'002'

300.0

'003'

null

[6]

Example where CONSTANT() is passed as the first argument instead of a regular table from the Data Model or DOMAIN_TABLE. The result is an aggregate and can be used together with other aggregate values. Here, the result of the PU_TRIMMED_MEAN operator is added to the result of the SUM operator:

Query

Column1

         ADD ( PU_TRIMMED_MEAN ( CONSTANT ( ) , "caseTable"."value" , 20 , 20 ) , SUM ( "caseTable"."value" ) )
        

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

null

4

'001'

500

5

'001'

200

6

'001'

300

7

'001'

null

8

'001'

600

9

'002'

300

10

'002'

300

11

'002'

500

12

'002'

100

13

'002'

null

14

'002'

400

15

'002'

500

16

'003'

200

17

'003'

400

18

'003'

100

19

'003'

100

20

'003'

null

companyDetail

companyCode : string

country : string

'001'

'DE'

'002'

'DE'

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : float

5850.0

[7]

Example over three tables: For each entry in table B, calculate the trimmed mean with default lower & upper cutoffs of 5% of the values that are larger than 100 in table C. In this case, a lower & upper cutoff of 5% is rounded to 0 rows, which produces the same result as PU_AVG. Tables B and C do not have a direct connection, but are connected via table A:

Query

Column1

         "B"."B_KEY"
        

Column2

         PU_TRIMMED_MEAN ( "B" , "C"."VALUE" , "C"."VALUE" > 100 )
        

Input

Output

A

B_KEY : int

C_KEY : string

VALUE : int

1

'A'

100

1

'B'

200

2

'C'

300

2

'D'

400

3

'E'

500

3

'F'

600

B

B_KEY : int

1

2

C

C_KEY : string

VALUE : int

'A'

400

'A'

100

'A'

200

'B'

100

'C'

200

'D'

500

Foreign Keys

A.C_KEY

C.C_KEY

B.B_KEY

A.B_KEY

Result

Column1 : int

Column2 : float

1

300.0

2

350.0

See also: