Description
Filters can be defined as Analysis filters, Sheet filters or Component filters. If a query is sent to Celonis, all active filters are propagated to the requested table(s). Multiple filters on a table are merged together by a logical AND.
Filter Propagation
Filter propagation is necessary if there are one or more tables on which a filter is applied, which are not the same as the result table. In that case Celonis propagates the filters to the result table, along the specified join graph. For more information on the join graph see Joins).
Stable Filter
All filters in Celonis are stable. Stable in this context means that filters don't interfere with each other. As a result of this, the order of the filters doesn't matter. For example:
FILTER table.col > 400; FILTER table.col < 600;
returns the same as
FILTER table.col < 600; FILTER table.col > 400;
Filter stability is also the reason why Celonis doesn't support filters on aggregations, because they would not be stable. For example:
FILTER table.col > 400; FILTER AVG(table.col) < 400;
can return a different result than
FILTER AVG(table.col) < 400; FILTER table.col > 400;
Comparison against NULL
Applying a filter which compares a column against null or not null always returns an empty result. To filter on all not null values use the ISNULL function. Null represents an unknown value. Celonis can not be sure if two unknown values are not the same.
Syntax
FILTER [FORCED] condition;
Forced Filter
If a regular filter is set as a sheet or component filter, the affected Dropdown and Button Dropdown components still show all available values. If only those values which respect the filter should be displayed and selectable, a forced filter can be used. Analysis filters are forced by default.
Example
Dropdown component using this regular sheet or component filter:
FILTER "Table"."Country" IN ('DE','US');
All values are available in the dropdown menu. Values which do not match the filter condition are displayed in gray color.
Dropdown component using this forced sheet or component filter:
FILTER FORCED "Table"."Country" IN ('DE','US');
Only values which match the filter condition are available.
Examples
Filter
FILTER "Numbers"."number" IN (13, 34)
Filter
FILTER "Numbers"."id" IN (2, 3)
Column1
"Numbers"."id"
Column2
"Numbers"."number"
Filter
FILTER "Numbers"."number" IN (13, 34)
Column1
SUM("Numbers"."number")
Filter
FILTER "Numbers"."number" NOT IN (13, 34)
Filter
FILTER "Numbers"."id" = 1
Column1
"Numbers"."id"
Column2
"Numbers"."number"
Filter
FILTER "companyDetail"."country" = 'DE'
Column1
"caseTable"."caseId"
Filter
FILTER "caseTable"."value" > 300
Column1
"caseTable"."caseId"
Column2
"companyDetail"."country"