When referencing objects, you may want to filter data to keep only certain items of list or values. In that case, you will need to use the modifier FILTER.
The EXCLUDE modifier in Pigment allows you to filter out specific data from a Block based on cell values or associated items. This is particularly useful when you want to remove certain values or items from your analysis. For more information, see EXCLUDE modifier.
Syntax of a filtering operation
The FILTER operation requires a boolean expression:
expressionrFILTER: filtering_expression]
This filtering_expression can filter data based on Dimension items (filtering Countries, Employees, Product, etc.) or on values (filtering quantities lower than a certain amount, etc.)
Filtering items of Lists
The filtering expression can take single items of Lists:
List = List."Item"List <> List."Item"
It works also with range of items:
List IN (List."Item1", List."Item2", ..., List."ItemX")
Examples
If the source Metric or Property you want to use in a formula is based on a List (Country for example) where you only need one or a few items (FR and UK for example), then you may use this syntax to reference items of Lists in the filtering expression:
| Case | Returns |
|
| Returns the |
|
| Returns the |
|
| Returns the |
Filtering values
It is also possible to filter on values to only keep certain cells that match the filtering expression.
| Case | Returns |
|
| The Price per Product only if the |
|
| The |
You can also define complex filtering expressions:
| Case | Returns |
|
| The |
|
| Returns the |
Filtering values with CurrentValue
Filtering based on values of the “expression” is a common use case. The simplest way of doing it is to duplicate the expression in the filtering expression.
ex: RevenuesFILTER: Revenue > 1000]
However, sometimes the expression filtered can be long. ex:
(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’) FILTER: (‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’) > 1000]
Pigment also offers a specific keyword: CurrentValue to avoid duplication the expression returning values. The previous formula can be written:
(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’)aFILTER: CurrentValue > 1000]
Note
When an expression is written without keywords between brackets, Pigment will consider it a filtering operation.
For example, RevenuenRevenue > 1000] is the same as RevenuetFILTER: Revenue > 1000].
See also