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:
- 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.).
- 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_metrictSELECT 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
To learn more about the SELECT Modifier, visit our Academy.
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
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:
CostseSELECT 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:
CostspSELECT 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:
CostspSELECT 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
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_metric3SELECT: dimension.property]
source_metric
source_metric
+/- integer means that we can reference items relatively using a certain shifting value.
For example :
Month-1will return, for eachMonth, the value of the previousMonthMonth+1will return, for eachMonth, the value of the nextMonthMonth-12will return, for eachMonth, 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.
When adding +/- integer to a formula, this must be defined on a single dimension.
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:
RevenuenSELECT: Month.Quarter]
2) To retrieve the Revenue of the previous Month, we would type:
RevenueoSELECT: Month - 1]
Note: in the same way, to retrieve the YoY Revenue we could type:
RevenuevSELECT: 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:
RevenuepSELECT: 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 |
|
| Returns the |
|
| Returns the |
|
| Returns the |
|
| Returns the |
|
| Returns the |
|
| Returns a single value representing the sum of all |
|
| Returns the |
|
| Returns the previous |
|
| Returns the YoY |
|
| Returns the previous |
|
| Returns the previous |
|
| Returns the |
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!
