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
.
If you need to filter the View of a Metric while keeping the underlying data, please refer to this documentation: Playing with Views
Syntax of a filtering operation
The FILTER
operation requires a boolean expression:
expressionsFILTER: 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 duplicatate the expression in the filtering expression.
ex: RevenueoFILTER: 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 dupplication the expression returning values. The previous formula can be written:
(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’) FILTER: CurrentValue > 1000]
Note
When an expression is written without keywords between brackets, Pigment will consider it a filtering operation.
For example, RevenuebRevenue > 1000]
is the same as RevenueoFILTER: Revenue > 1000]
.
See also