SELECT modifier

  • 21 December 2021
  • 0 replies
  • 545 views

Userlevel 3
Badge +2
  • Community Manager
  • 8 replies

Description

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 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]

Note: this will always return a single cell.

 

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

 

Case

Results

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

Returns the Costs selected on the Sales Team

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

Returns the Costs selected and summed on the Sales and Operation Team

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

Returns the Costs selected and averaged on the EMEA Region

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

Returns the Costs selected and summed on the Sales and Operation Team for France 

Costs[SELECT SUM: Selection]

Returns the Costs selected and summed based on the Selection boolean metric

Costs[SELECT SUM: Costs > 100]

Returns a single value representing the sum of all Costs granular cells over 100.

Revenue[SELECT: Month.Quarter]

Returns the Quarter Revenue

Revenue[SELECT: Month - 1]

Returns the previous Month Revenue

Revenue[SELECT: Month - 12]

Returns the YoY Revenue

Revenue[SELECT: Month.Quarter - 1]

Returns the previous Quarter Revenue

Revenue[SELECT: Month.Year - 1]

Returns the previous Year Revenue

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

Returns the Quarter Revenue of the previous Month

 

 

Excel equivalent: none

See alsoBYADDFILTERREMOVE

 


0 replies

Be the first to reply!

Reply