---
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: 2025-06-10T15:11:10Z
published: 2025-08-22T10:56:55Z
---

> ## 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**

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**](/v1/docs/select-modifier#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**](/v1/docs/select-modifier#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](/v1/docs/select-modifier#summary-of-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](/v1/docs/filter-modifier) and [REMOVE](/v1/docs/remove-modifier) on one or multiple dimensions.

### Syntax

```plaintext
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.](/v1/docs/aggregating-data-in-formulas)

### Return type

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

> [!NOTE]
> To learn more about the [SELECT Modifier](https://academy.gopigment.com/path/write-and-test-formulas/select-modifier), visit our Academy.

### Examples

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/c4c2baf2-736b-488e-91be-5a616f370a84.png)

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

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

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

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

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

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/b72a05d6-c9f8-46c6-a92d-5f7228d5ddea.png)

1. In the previous example, `SUM` is optional. In fact, by default as explained on the [Aggregation Methods](/v1/docs/aggregating-data-in-formulas) article, Pigment applies a sum on numbers. We could have changed the aggregator to average:

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/ed09a67d-1ca0-4d61-9e00-54cb8610aeaf.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:

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/6494b38f-2eb2-47eb-9f7c-3b5222badab2.png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/ac95b9da-8a01-49a6-abc2-629d57813f2c.png)

1. Of course, we can also select items from multiple Dimensions, in our case `Team` and `Country`:

```plaintext
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/7268d491-5cff-4398-85e5-412cd4b4b7e6.png)

Note: it returns a single cell in that example since we selected items from all the available Dimensions: `Team` and `Country`.

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

```plaintext
Costs[SELECT SUM: Costs > 100]
```

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/d6bab52c-7606-41e7-8341-c736959ccd61.png)

> [!TIP]
> Note: this will always return a single cell.

1. 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:

```plaintext
Costs[SELECT SUM: Selection]
```

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/71c5589b-ef89-4e60-b050-e377707dc491.png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/dba55e72-071c-4149-abbe-8c79bbdb436b.png)

## 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](/v1/docs/aggregating-data-in-formulas) on a Dimension followed by an allocative [BY](/v1/docs/aggregating-data-in-formulas).

### 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.

> [!TIP]
> 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`):

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/7a84cfc2-de9f-4bcb-976d-48cc4cfebdc6.png)

1. To get the`QuarterRevenue` of each `Month`, we would type:

```plaintext
Revenue[SELECT: Month.Quarter]
```

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/ac46795c-4101-436b-97dd-1b0153620e08.png)

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

```plaintext
Revenue[SELECT: Month - 1]
```

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/e7e0060e-775d-42e8-9597-bf463b0afb00.png)

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

```plaintext
Revenue[SELECT: Month - 12]
```

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

```plaintext
Revenue[SELECT: Month.Quarter - 1]
```

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/00121581-4fcc-4671-b84d-4aefcddc54ad.png)

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

```plaintext
Revenue[SELECT: Month.Year - 1]
```

Or even get the `Quarter` value of the previous month:

```plaintext
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 also**: [BY](/v1/docs/aggregating-data-in-formulas), [ADD](/v1/docs/add-modifier), [FILTER](/v1/docs/filter-modifier), [REMOVE](/v1/docs/remove-modifier)

> [!TIP]
> 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!