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:
a table from the data model. It needs to be, directly or indirectly, connected to the source_table, and there must be a 1:N relationship between the target_table and the source_table. Further documentation about join relationships can be found in Join functionality.
DOMAIN_TABLE or CONSTANT (see Pull Up Aggregation Table Options).
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. | |||||||||||||||||
| |||||||||||||||||
|
[2] Calculate the trimmed mean of the case table values for each company code with the lower cutoff & upper cutoff set to 20%: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||
|