Filtering data in formulas

  • 21 December 2021
  • 0 replies
  • 1822 views

Userlevel 5
Badge +6
  • Community Manager
  • 20 replies

 

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:

expression[FILTER: 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

Product.Price[FILTER: Product.Category = Category."A"]

Returns the Price of all the Products included in the Category "A"

Revenue[FILTER: Country = Country."FR" OR Country = Country."UK"]

Returns the Revenue of France and UK.

Transactions.Costs[FILTER: FIND("ABC", Transactions.Supplier) > 0]

Returns the Cost Property of the Transactions that contains "ABC" in the Transactions Supplier name.

 

 

Filtering values

 

It is also possible to filter on values to only keep certain cells that match the filtering expression.

 

Case

Returns

Product.Price[FILTER: Product.Price > 10]

The Price per Product only if the Price is higher than 10.

Revenue[FILTER: Revenue > 1000]

The Revenue by its original Dimensions only if the Revenue is higher than 1000.

 

You can also define complex filtering expressions:

 

Case

Returns

Revenue[FILTER: Revenue[by: country.region] > 1000]

The Revenue by its original Dimensions only if the aggregated Revenue by Region is higher than 1000.

Transactions.Amount[FILTER: FIND("ABC", Transactions.Supplier)<>0]

Returns the Amount per Transactions, only if the Transaction Supplier contains ABC in its default Property.

 

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: Revenue[FILTER: 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, Revenue[Revenue > 1000] is the same as Revenue[FILTER: Revenue > 1000].

 

See also

Function by category

 

 


0 replies

Be the first to reply!

Reply