SELECT modifier

Description

Filters and aggregates a Metric's data or offsets its data according to its Dimensions.

The SELECT modifier can be used in 2 different cases:

1. to filter and aggregate a Metric according to one or multiple items (ex: get Metric's data of a specific month or the aggregated value of several month and countries, etc.).
2. to offset Metric's data according to one or more of its Dimension or Dimension's Property (ex: get the previous month, the previous year, etc.) 

You can follow the examples below with detailed screenshots or scroll to the end of the article to see a summary of all examples.

Filter and Aggregate

SELECT can first be used to filter and aggregate data. It is useful when you need to "pick" a value (single or aggregated) from a Metric in a single step. It is equivalent to using FILTER and REMOVE on one or multiple dimensions.

Syntax

source_metric[SELECT AGGREGATOR: boolean operation or metric]

The default aggregator, if omitted, is SUM for number objects. Other aggregators are listed here: Aggregation Methods.

Return type

same as source object except when using a COUNT aggregator on non-number types

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: As you can see, it returns the Costs data without the Team Dimension. As mentioned above, it is 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. In fact, by default as explained on the Aggregation Methods article, Pigment applies a sum on numbers. 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: it returns a single cell in that example since we selected items from all the available Dimensions: Team and Country.

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

Costs[SELECT SUM: Costs > 100]

7) Finally, SELECT not only works with hardcoded operations like in the above examples, we can also pass a boolean-type Metric that can be either inputed or calculated with a formula:

Costs[SELECT SUM: Selection]

Offset

SELECT can also be used to shift a Metric's data according to its Dimension items (previous month of a Metric defined by month) or its Dimensions Properties (previous year of a Metric defined by month). For the more technical ones, it is equivalent to using an aggregative BY on a Dimension followed by an allocative BY.

Syntax

source_metric[SELECT: dimension.property]

source_metric[SELECT: dimension +/- integer]

source_metric[SELECT: dimension.property +/- 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's worth mentioning, that it works with any Dimension based on its items order. It feels natural with Time Dimensions like Month but let's say that you use a Dimension Assumptions, you could type Assumptions-1 to get the value of the previous assumption.

Return type

same as source object

Examples

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

1) To get theQuarterRevenue of each Month, we would type:

Revenue[SELECT: Month.Quarter]

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

Revenue[SELECT: Month - 1]

Note: in the same way, to retrieve the YoY Revenue we could type:

Revenue[SELECT: Month - 12]

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

Revenue[SELECT: Month.Quarter - 1]

In the same way, to retrieve the Last Year Revenue we could type:

Revenue[SELECT: Month.Year - 1]

Or even get the Quarter value of the previous month:

Revenue[SELECT: (Month -1 ).Quarter]

Summary of examples

Excel equivalent: none

See also: BY, ADD, FILTER, REMOVE

More of a hands-on learner? 

Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace.  It includes examples of every formula and modifier in Pigment!