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

# window - Measures

> Applies a SQL window function to another measure, such as a running total, rank, or lag.

Applies a SQL window function to the result of another measure, such as a running total, a rank within a group, or the value from a previous row. Use `partition_by` to split the rows into separate windows, `order_by` to sort the rows within each window, and `offset_start`/`offset_end` to set the frame of rows the function considers.

The measure's `sql` must reference other measures, not raw columns or dimensions. Define the aggregate you want to apply the window function to as its own measure first.

> [!NOTE]
>
> Window measures can only be edited in the model IDE.

## How it works

This `cumulative_arr_change` measure takes the `arr_change` measure and sums it again, creating a running total ordered by date:

```yaml Model theme={null}
cumulative_arr_change:
  sql: SUM(${arr_change})
  window:
    order_by:
      - field: arr_dates.date
```

```sql SQL equivalent theme={null}
SELECT arr_dates.date,
       SUM(arr_change) OVER (ORDER BY arr_dates.date) AS "cumulative_arr_change"
FROM (
    SELECT arr_dates.date,
           SUM(change) AS arr_change
      FROM arr_dates
  GROUP BY arr_dates.date
) arr_change_by_date
```

The inner query computes `arr_change` per date, same as it would without a `window`. The `window` block then tells Omni to apply the measure's `sql` — `SUM(${arr_change})` — as a window function over those per-date rows, ordered by `arr_dates.date`. Because  neither `offset_start` or `offset_end` is set, the frame defaults to every row from the start of the partition through the current row, producing a running total.

## Syntax

```yaml theme={null}
<measure_name>:
  sql: <window_function_over_measure_refs>
  window:
    partition_by: [<view>.<field>, ...]
    order_by:
      - field: <view>.<field>
        desc: <true_false>
        null_sort: <nulls_first_nulls_last>
    offset_start: <integer>
    offset_end: <integer>
    offset_type: <rows_range>
```

## Properties

**measure\_name** `object`

The name of the measure. Measure names must:

* Be unique within the view
* Start with a letter
* Contain only alphanumeric characters and underscores

**measure\_name properties**

**window** `object`

Makes the measure a SQL window function, applied to the measure's own `sql`.

**window properties**

**partition\_by** `array`

An array of dimension references, specified as `<view>.<field>`. Splits the rows into separate windows; the window function restarts in each partition. Fields referenced here are automatically added to the query.

**order\_by** `array`

An array of sort entries that order the rows within each window.

**order\_by properties**

**field** `string` *required*

The field to sort by, specified as `<view>.<field>`. This field is automatically added to the query.

**desc** `boolean` (default: false)

Sorts the field in descending order when `true`. Defaults to ascending order.

**null\_sort** `string`

Controls where null values sort, either `nulls_first` or `nulls_last`.

**offset\_start** `integer`

Where the window frame starts, counted from the current row: negative for rows before it, `0` for the current row, positive for rows after it. Omit to start the frame at the first row of the partition.

**Note**: Ranking and offset functions (`RANK`, `ROW_NUMBER`, `LAG`, and similar) don't take a frame, so `offset_start` and `offset_end` don't apply to these functions.

**offset\_end** `integer`

Where the window frame ends, counted from the current row the same way as `offset_start`. Omit to end the frame at the current row when `order_by` is set, or at the last row of the partition when it isn't.

**offset\_type** `string` (default: rows)

What `offset_start` and `offset_end` count: `rows`, or `range`, the units of the single `order_by` value, so rows that share a value share a frame. Defaults to `rows` once an offset is set.

## Examples

```yaml title="Cumulative sum" theme={null}
measures:
  cumulative_arr_change:
    sql: SUM(${arr_change})
    window:
      order_by:
        - field: arr_dates.date
```

```yaml title="Trailing 3-period average" theme={null}
measures:
  trailing_3_period_arr_change:
    sql: AVG(${arr_change})
    window:
      order_by:
        - field: arr_dates.date
      offset_start: -2
```

```yaml title="Rank within a partition" theme={null}
measures:
  arr_change_rank:
    sql: RANK()
    window:
      partition_by: [customers.region]
      order_by:
        - field: arr_change
          desc: true
```

```yaml title="Lag" theme={null}
measures:
  previous_arr_change:
    sql: LAG(${arr_change}, 1)
    window:
      order_by:
        - field: arr_dates.date
```
