SELECT modifier

Description

The SELECT modifier applies one of the following transformations to a Metric:

1. Filter & remove: filters data on a Boolean expression, then removes the relevant Dimensions.

2. Offset / Mapping: reads the value of another Item of the same Dimension based on the indicated mapping expression.

3. Aggregate & allocate: aggregates data based on the indicated mapping expression then allocates it back to source Items.

ℹ️ Note

A simple way to validate which transformation SELECT is applying is to open the hint. For more information, see Hints and troubleshooting below.

To learn more about the SELECT Modifier, visit our Academy.

Syntax

Return type

In most cases, SELECT returns the same data type as the source Metric. If you use aggregation methods such as COUNT on non-number types, the return type can differ.

For more details on aggregation behavior, see Aggregation and allocation methods.

Filter & remove (Boolean selection)

Behavior

When the expression following the SELECT modifier is a Boolean expression, then SELECT filters data according to the expression and removes the relevant List(s) using the specified aggregation method.

• Default aggregation method is typically SUM for Number / Integer.

• For other data types, you may need to specify the aggregation method explicitly.

Examples

Let's take a Costs Metric defined by Team and Country:

1. To retrieve the Costs of a specific Team , we would type the following formula:

Costs[SELECT: Team = Team."Sales"]

ℹ️ Note

This returns the Costs data without the Team Dimension, equivalent to using FILTER and REMOVE:

(Costs[FILTER: Team = Team."Sales"][REMOVE: Team])

2. We can also select multiple Items of the same Dimension and aggregate them directly:

Costs[SELECT SUM: Team = Team."Sales" OR Team = Team."Operations"]

3. In the previous example, SUM is optional as Pigment applies a sum on numbers by default. We could have changed the aggregator to average:

Costs[SELECT AVG: Team = Team."Sales" OR Team = Team."Operations"]

4. The SELECT modifier can also select multiple Items using a Dimension's Property. In this case, let's use the Region Property of the Country Dimension to select several countries:

Costs[SELECT AVG: Country.Region = Region."EMEA"]

5. Of course, we can also select Items from multiple Dimensions, in our case Team and Country:

Costs[SELECT SUM: (Team = Team."Sales" OR Team = Team."Operations") AND Country = Country."France"]

ℹ️ Note

This example returns a single cell since we selected Items from all the available Dimensions: Team and Country.

6. It is also possible to use the Metric's value itself as a selecting method:

Costs[SELECT SUM: Costs > 100]

ℹ️ Note

This always returns a single cell.

7. Finally, SELECT works with hardcoded operations like the above examples, but can also pass a Boolean-type Metric that can be either inputed or calculated with a formula:

Costs[SELECT SUM: Selection]

Offset

Behavior

SELECT can be used to shift a Metric's data according to its Dimension Items (such as the previous month of a Metric defined by month) or its Dimension Properties (such as the previous year of a Metric defined by month). In more technical terms, it is equivalent to using an aggregative BY on a Dimension followed by an allocative BY.

Syntax

source_metric[SELECT: dimension +/- integer]

+/- integer means that we can reference Items relatively using a certain shifting value.

For example :

- Month-1 will return, for each Month, the value of the previous Month

- Month+1 will return, for each Month, the value of the next Month

- Month-12 will return, for each Month, the YoY value

It works with any Dimension based on its Item’s order, whether a time Dimension like Month or a non-time Dimension like Assumptions: you could type Assumptions-1 to get the value of the previous assumption.

ℹ️ Note

When adding +/- integer to a formula, the expression should be defined on a single List for the offset to be unambiguous.

Examples

Take a Revenue Metric defined by Country and Month (and we display the total by Quarter):

To retrieve the Revenue of the previous Month, we would type:

Revenue[SELECT: Month - 1]

To retrieve the year-over-year Revenue value, we would type:

Revenue[SELECT: Month - 12]

Mapping

What is a mapping expression?

A mapping expression defines a relationship between:

• a source List Item (or several source Lists)

• a target Item (often in the same List or a target Dimension)

using one of the following:

• a List Property of type Dimension

• a Metric with data type set to Dimension

For more information on mapping Metrics, see Make a mapping Metric.

Behavior

Mapping reads the value of another Item indicated by the mapping expression.

Syntax

source_metric[SELECT: mapping_expression]

Example

DimensionA.TargetItem points from each Item in DimensionA to its Property TargetItem where the Property has data type Boolean or Dimension.

A Metric with the following expression reads the value at the mapped target Item for each current Item.

source_metric[SELECT: DimensionA.TargetItem]

Let’s assume you have a Dimension City which has a subset Property France only, a Boolean.

Your source Metric Sales_EU contains the following data:

You can retrieve the sum of sales for French cities using this expression:

Sales_EU [select: City_EU.'France only']

Aggregate & allocate (through a mapping expression)

Behavior

SELECT aggregates data based on the mapping relationship (the default aggregation method is SUM), then allocates it back to source Items (commonly using CONSTANT allocation).

Syntax

source_metric[SELECT: dimension.property]

source_metric[SELECT: dimension.property +/- integer]

Example

Imagine a Revenue Metric defined by Country and Month, displaying the total by Quarter.

1. To have revenue aggregated by quarter and reallocated under the CONSTANT allocation method to each  Month, we would type:

Revenue[SELECT: Month.Quarter]

ℹ️ Note

This aggregates Revenue from the Month level to the Quarter level (e.g. Jan+Feb+Mar → Q1), then allocates this Quarter total back to each Month of the Quarter. As a result, all Months in the same Quarter display the same value: the total for the Quarter.

2. We can also group data using a Dimension's Property and offset with an integer. In this example, we want the value of the previous Quarter in each Month:

Revenue[SELECT: Month.Quarter - 1]

Similarly, to retrieve the Revenue for the prior year, we would type:

Revenue[SELECT: Month.Year - 1]

Hints and troubleshooting

Contextual hints are available that explain:

• which List(s) are being removed or replaced.

• whether the operation is Filter & remove, Offset, Mapping, or Aggregate & allocate.

• which aggregation / allocation method is being applied (and defaults if not specified).

• any incompatibilities or conflicts.

To open the hints, hover your pointer over SELECT. A pop-up appears providing the hint.

Warnings

Warnings may appear in the situations below.

Conflicting Lists in the output

The transformation would produce an output already defined on a List that is also produced by the SELECT logic, resulting in a conflict.

Incompatible mapping expression

The mapping expression does not include Lists compatible with the source Metric, so the operation would require implicit allocation/aggregation that may be unintended.

Offset ambiguity

+/- N is being used in a context where the expression is not clearly defined on a single List.

Excel equivalent: none

See also: BY modifier, FILTER modifier, REMOVE modifier, Aggregation and allocation methods