Pull Up Aggregation
Description
The Pull-Up-functions allow you to aggregate a column based on another table. You can define the target table to which the entries of a column from another table are pulled, and you can explicitly define on which basis calculations are executed.
Pull-Up-functions are used to achieve nested aggregations and filters on aggregations. Both cannot be done by standard aggregations. Celonis deliberately does not support nested standard aggregations in order to avoid subqueries which would significantly increase the language complexity. Applying filters on standard aggregations is not supported because the resulting filters would not be stable, as explained in filters.
The Pull-Up-functions support nested aggregations and filters on aggregations by applying filters differently than the standard aggregations. Standard aggregations take the current filter state into account and recalculate their result every time a filter changes. In contrast to that the Pull-Up-functions ignore the global filter state and are calculated only once. Actually Pull-Up-functions dynamically extend a data model. Therefore the result of a Pull-Up-function can be used like a column of a table. For example it can be used as an input for another aggregation or as basis for a filter.
Filter expressions can be defined to specify which values should be taken into account for the aggregation.
A 1:N relationship between the target table and the table of the specified source column is required.
For a 1:N:1 relationship, the BIND operator can be used in PU-functions. It binds the values of the right table to the middle table, hence these values can be used then. An example can be found in the BIND documentation.
Syntax
PU_X ( target_table, source_table.column [, filter_expression] )
PU_X ( target_table, source_table.column [, filter_expression] [, ORDER BY source_table.column] )
PU_QUANTILE ( target_table, source_table.column, quantile [, filter_expression] )
PU_TRIMMED_MEAN ( target_table, source_table.column [, lower_cutoff [, upper_cutoff ] ] [, filter_expression ] )
PU_STRING_AGG ( target_table, source_table.column, delimiter [, filter_expression] [, ORDER BY source_table.column [ASC|DESC] ] )
The following arguments can be passed to all PU-functions:
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.
filter_expression (optional): An optional filter expression to specify which values of the source_table.column should be taken into account for the aggregation.
Filter behavior
The way PU-functions handle filters is different compared to the standard aggregation. In contrast to the standard aggregation, PU-functions ignore filters, meaning that if a filter or a selection is changed, the result of the PU-function is not recalculated. Another difference to the standard aggregation is that it is possible to filter on the result of a PU-function.
PU-functions can be made FILTER-aware by using FILTER_TO_NULL inside the input column. In that case, however, it is not possible anymore to filter on the result of the PU-function. Please also read the documentation of FILTER_TO_NULL about further possible side effects when using that functionality.
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), 0 (PU_COUNT, PU_COUNT_DISTINCT) or NULL (all other PU-functions) will be returned. NULL values in the source table column are treated as if the row does not exist.