---
title: "Use the SELECT Modifier to Filter and Offset Metrics"
slug: "select-modifier"
description: "Learn how the SELECT modifier in Pigment lets you manipulate Metrics—filtering, aggregating, or shifting data by Dimension."
tags: ["Aggregate Data", "Filter Data", "Modifiers", "Offset Metric Data", "SELECT"]
updated: 2026-06-11T08:31:37Z
published: 2026-06-11T08:31:37Z
canonical: "kb.pigment.com/select-modifier"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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]
> ℹ️ Note
> 
> A simple way to validate which transformation `SELECT` is applying is to open the hint. For more information, see [Hints and troubleshooting](/v1/docs/select-modifier#hints-and-troubleshooting1) below.
> 
> To learn more about the [**SELECT Modifier**](https://academy.gopigment.com/path/write-and-test-formulas/select-modifier), visit our Academy.

### Syntax

| Syntax pattern | Typical use case | Transformation applied |
| --- | --- | --- |
| `Metric[SELECT : boolean_expression]` or `Metric[SELECT AGG: boolean_expression]` | Pick one or more Items (or cells) and aggregate | **Filter & remove**: filters by the Boolean expression, then removes the selected List(s) from the output (aggregation method may be required / defaulted) |
| `Metric[SELECT: List +/- N]` | Previous / next Item, YoY shifts, neighbor lookup | **Offset**: reads values from the Item N steps before (−) or after (+) the current Item on the selected List (based on Item order) |
| `Metric[SELECT: List.Property +/- N]` | Offset within a grouped level (e.g. previous Quarter) | **Offset (grouped)**: reads values from a neighbor Item after projecting to the Property level, then applying the shift |
| `Metric[SELECT: mapping_expression]` | Read values from another Item indicated by a mapping | **Mapping**: reads values from the mapped target Item (mapping expression can be a List Property or a mapping Metric) |
| `Metric[SELECT: mapping_expression]` (cross-list mapping case) | Traverse to a target level then come back to source Items | **Aggregate & allocate**: aggregates through the mapping relationship (often default `SUM`), then allocates back to source Items (often `CONSTANT`) |

### 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](/v1/docs/aggregators-and-allocators).

## 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](/v1/docs/aggregators-and-allocators) explicitly.

### Examples

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(333).png)

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

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(334).png)

> [!NOTE]
> ℹ️ Note
> 
> This returns the `Costs` data without the `Team` Dimension, equivalent to using `FILTER` and `REMOVE`:
> 
> `(Costs[FILTER: Team = Team."Sales"][REMOVE: Team])`

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

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(335).png)

1. 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"]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(336).png)

1. 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"]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(337).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(338).png)

1. 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"]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(339).png)

> [!NOTE]
> ℹ️ Note
> 
> This example returns a single cell since we selected Items from all the available Dimensions: `Team` and `Country`.

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

`Costs[SELECT SUM: Costs > 100]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(340).png)

> [!NOTE]
> ℹ️ Note
> 
> This always returns a single cell.

1. 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]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(341).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(342).png)

## 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]
> ℹ️ 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`):

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(343).png)

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

`Revenue[SELECT: Month - 1]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(344).png)

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](https://kb.pigment.com/v1/docs/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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(345).png) Your source Metric `Sales_EU` contains the following data:

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(347).png)

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

`Sales_EU [select: City_EU.'France only']`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(348).png)

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(349).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image-1781022643685.png)

> [!NOTE]
> ℹ️ 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.

1. 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]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(351).png)

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](/v1/docs/by-modifier), [FILTER modifier](/v1/docs/filter-modifier), [REMOVE modifier](/v1/docs/remove-modifier), [Aggregation and allocation methods](/v1/docs/aggregators-and-allocators)