# Multi-stage calculations

[Measures][ref-measures] are usually calculated as aggregations over [dimensions][ref-dimensions]
or arbitrary SQL expressions.

_Multi-stage calculations_ enable data modeling of more sophisticated _multi-stage measures_.
They are calculated in two or more stages and often involve manipulations on already
aggregated data. Each stage results in one or more [common table expressions][link-cte]
(CTEs) in the generated SQL query.

<WarningBox>

Multi-stage calculations are powered by Tesseract, the [next-generation data modeling
engine][link-tesseract]. In versions before v1.7.0, it was not enabled by default.

</WarningBox>

<WarningBox>

Multi-stage calculations are not currently accelerated by pre-aggregations.
Please track [this issue](https://github.com/cube-js/cube/issues/8487).

</WarningBox>

Common uses of multi-stage calculations:

- [Rolling window](#rolling-window), e.g., cumulative counts or moving averages.
- [Time-shift](#time-shift), e.g., year-over-year sales growth.
- [Period-to-date](#period-to-date), e.g., year-to-date (YTD) analysis.
- [Conditional measure](#conditional-measure), e.g., amount in a selected currency.

**Some calculations use inner and outer aggregation stages.** The _inner_ stage computes
a base measure at a specific granularity, and the _outer_ stage aggregates those results
according to the query's dimensions:

- [Fixed dimension](#fixed-dimension), e.g., percent of total — use the [`group_by`][ref-group-by]
parameter to group by only the listed dimensions.
- [Nested aggregate](#nested-aggregate), e.g., average of per-customer averages — use the
[`add_group_by`][ref-add-group-by] parameter to group by query dimensions plus listed.
- [Ranking](#ranking), e.g., ranking products by revenue — use the [`reduce_by`][ref-reduce-by]
parameter to group by query dimensions minus listed.

## Rolling window

Rolling window calculations are used to calculate metrics over a moving window of time.
Use the [`rolling_window` parameter][ref-rolling-window] of a measure to define
a rolling window.

### Stages

Here's how the rolling window calculation is performed:

- **Date range.** First, the date range for the query is determined.
If there's a time dimension with a date range filter in the query, it's used.
Otherwise, the date range is determined by selecting the minimum and maximum
values for the time dimension.

<WarningBox>

Tesseract enables rolling window calculations without the date range for the time dimension.
If Tesseract is not used, the date range must be provided. Otherwise, the query would
fail with the following error: `Time series queries without dateRange aren't supported`.

</WarningBox>

- **Time windows.** Then, the series of time windows is calculated. The size of the
window is defined by the time dimension granularity and the `trailing` and
`leading` parameters.
- **Measure.** Finally, the measure is calculated for each window.

### Example

Data model:

```yaml

cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, '2025-01-01'::TIMESTAMP AS time UNION ALL
      SELECT 2 AS id, '2025-01-11'::TIMESTAMP AS time UNION ALL
      SELECT 3 AS id, '2025-01-21'::TIMESTAMP AS time UNION ALL
      SELECT 4 AS id, '2025-01-31'::TIMESTAMP AS time UNION ALL
      SELECT 5 AS id, '2025-02-01'::TIMESTAMP AS time UNION ALL
      SELECT 6 AS id, '2025-02-11'::TIMESTAMP AS time UNION ALL
      SELECT 7 AS id, '2025-02-21'::TIMESTAMP AS time UNION ALL
      SELECT 8 AS id, '2025-03-01'::TIMESTAMP AS time UNION ALL
      SELECT 9 AS id, '2025-03-11'::TIMESTAMP AS time UNION ALL
      SELECT 10 AS id, '2025-03-21'::TIMESTAMP AS time UNION ALL
      SELECT 11 AS id, '2025-03-31'::TIMESTAMP AS time UNION ALL
      SELECT 12 AS id, '2025-04-01'::TIMESTAMP AS time

    dimensions:
      - name: time
        sql: time
        type: time
 
    measures:
      - name: rolling_count_month
        sql: id
        type: count
        rolling_window:
          trailing: unbounded
```

Query and result:

<Screenshot src="https://ucarecdn.com/40179d09-3e10-4ada-8456-1deca6a5035d/" />

## Time shift

A _time-shift measure_ calculates the value of another measure at a different point in
time. This is achieved by _shifting_ the time dimension from the query in the necessary
direction during the calculation. Time-shifts are configured using the [`time_shift`
parameter][ref-ref-time-shift] of a measure.

Typically, this is used to compare the current value of a measure with its prior value,
such as the same time last year. For example, if you have the `revenue` measure, you can
calculate its value for the same time last year:

```yaml
- name: revenue_prior_year
  multi_stage: true
  sql: "{revenue}"
  type: number
  time_shift:
    - interval: 1 year
      type: prior
```

You can use time-shift measures with [calendar cubes][ref-calendar-cubes] to customize
how time-shifting works, e.g., to shift the time dimension to the prior date in a retail
calendar.

### Example

Data model:

```yaml
cubes:
  - name: prior_date
    sql: |
      SELECT '2023-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-06-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-07-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-08-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-09-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-10-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-11-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-12-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-01-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-02-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-03-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-06-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-07-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-08-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-09-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-10-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-11-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-12-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-01-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-02-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-03-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-06-01'::TIMESTAMP AS time, 1000 AS revenue

    dimensions:
      - name: time
        sql: time
        type: time

    measures:
      - name: revenue
        sql: revenue
        type: sum

      - name: revenue_ytd
        sql: revenue
        type: sum
        rolling_window:
          type: to_date
          granularity: year

      - name: revenue_prior_year
        multi_stage: true
        sql: "{revenue}"
        type: number
        time_shift:
          - time_dimension: time
            interval: 1 year
            type: prior
            
      - name: revenue_prior_year_ytd
        multi_stage: true
        sql: "{revenue_ytd}"
        type: number
        time_shift:
          - time_dimension: time
            interval: 1 year
            type: prior
```

Queries and results:

<Screenshot src="https://ucarecdn.com/fdc8dba1-6c9e-4682-87b9-1435f8eac0ca/" />

<Screenshot src="https://ucarecdn.com/d60c3ec2-07e0-47c3-adcd-1f7fe2c75bc9/" />

## Period-to-date

Period-to-date calculations can be used to analyze data over different time periods:

- Year-to-date (YTD) analysis.
- Quarter-to-date (QTD) analysis.
- Month-to-date (MTD) analysis.

```yaml
- name: revenue_ytd
  sql: revenue
  type: sum
  rolling_window:
    type: to_date
    granularity: year
  
- name: revenue_qtd
  sql: revenue
  type: sum
  rolling_window:
    type: to_date
    granularity: quarter
  
- name: revenue_mtd
  sql: revenue
  type: sum
  rolling_window:
    type: to_date
    granularity: month
```

### Example

Data model:

```yaml
cubes:
  - name: prior_date
    sql: |
      SELECT '2023-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-06-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-07-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-08-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-09-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-10-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-11-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2023-12-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-01-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-02-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-03-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-06-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-07-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-08-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-09-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-10-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-11-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2024-12-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-01-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-02-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-03-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-04-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-05-01'::TIMESTAMP AS time, 1000 AS revenue UNION ALL
      SELECT '2025-06-01'::TIMESTAMP AS time, 1000 AS revenue

    dimensions:
      - name: time
        sql: time
        type: time

    measures:
      - name: revenue_ytd
        sql: revenue
        type: sum
        rolling_window:
          type: to_date
          granularity: year
        
      - name: revenue_qtd
        sql: revenue
        type: sum
        rolling_window:
          type: to_date
          granularity: quarter
        
      - name: revenue_mtd
        sql: revenue
        type: sum
        rolling_window:
          type: to_date
          granularity: month
```

Query and result:

<Screenshot src="https://ucarecdn.com/3c8bbb38-b582-410d-8a1c-fa7c0dc38d14/" />

## Conditional measure

Conditional measure calculations can be used to create measures that depend on the value
of a dimension. Such measures are defined using the [`case` parameter][ref-case-measures]
and used together with [`switch` dimensions][ref-switch-dimensions].

```yaml
- name: amount_in_currency
  multi_stage: true
  case:
    switch: "{CUBE.currency}"
    when:
      - value: EUR
        sql: "{CUBE.amount_eur}"
      - value: GBP
        sql: "{CUBE.amount_gbp}"
    else:
      sql: "{CUBE.amount_usd}"
  type: number
```

### Example

Data model:

<CodeTabs>

```yaml
cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, 100 AS amount_usd UNION ALL
      SELECT 2 AS id, 200 AS amount_usd UNION ALL
      SELECT 3 AS id, 300 AS amount_usd UNION ALL
      SELECT 4 AS id, 400 AS amount_usd UNION ALL
      SELECT 5 AS id, 500 AS amount_usd

    dimensions:
      - name: currency
        type: switch
        values:
          - USD
          - EUR
          - GBP

    measures:
      - name: amount_usd
        sql: amount_usd
        type: sum

      - name: amount_eur
        sql: "{amount_usd} * 0.9"
        type: number

      - name: amount_gbp
        sql: "{amount_usd} * 0.8"
        type: number

      - name: amount_in_currency
        multi_stage: true
        case:
          switch: "{currency}"
          when:
            - value: EUR
              sql: "{amount_eur}"
            - value: GBP
              sql: "{amount_gbp}"
          else:
            sql: "{amount_usd}"
        type: number
```

```javascript
cube(`orders`, {
  sql: `
    SELECT 1 AS id, 100 AS amount_usd UNION ALL
    SELECT 2 AS id, 200 AS amount_usd UNION ALL
    SELECT 3 AS id, 300 AS amount_usd UNION ALL
    SELECT 4 AS id, 400 AS amount_usd UNION ALL
    SELECT 5 AS id, 500 AS amount_usd
  `,

  dimensions: {
    currency: {
      type: `switch`,
      values: [`USD`, `EUR`, `GBP`]
    }
  },

  measures: {
    amount_usd: {
      sql: `amount_usd`,
      type: `sum`
    },

    amount_eur: {
      sql: `${amount_usd} * 0.9`,
      type: `number`
    },

    amount_gbp: {
      sql: `${amount_usd} * 0.8`,
      type: `number`
    },

    amount_in_currency: {
      multi_stage: true,
      case: {
        switch: `${currency}`,
        when: [
          { value: `EUR`, sql: `${amount_eur}` },
          { value: `GBP`, sql: `${amount_gbp}` }
        ],
        else: { sql: `${amount_usd}` }
      },
      type: `number`
    }
  }
})
```

</CodeTabs>

Query and result:

<Screenshot src="https://lgo0ecceic.ucarecd.net/43df5a7c-7537-448a-999c-abeed5347519/" />

## Fixed dimension

Fixed dimension calculations can be used to perform fixed comparisons, e.g., to compare
individual items to a broader dataset. Use the [`group_by` parameter][ref-group-by]
of a multi-stage measure to specify dimensions for the inner aggregation stage.

For example, comparing revenue sales to the overall average:

```yaml
- name: revenue
  sql: revenue
  format: currency
  type: sum
  
- name: occupied_sq_feet
  sql: occupied_sq_feet
  type: sum

- name: occupied_sq_feet_per_city
  multi_stage: true
  sql: "{occupied_sq_feet}"
  type: sum
  group_by:
    - city
    - state

- name: revenue_per_city_sq_feet
  multi_stage: true
  sql: "{revenue} / NULLIF({occupied_sq_feet_per_city}, 0)"
  type: number
```

Percent of total calculations:

```yaml
- name: revenue
  sql: revenue
  format: currency
  type: sum

- name: country_revenue
  multi_stage: true
  sql: "{revenue}"
  type: sum
  group_by:
    - country

- name: country_revenue_percentage
  multi_stage: true
  sql: "{revenue} / NULLIF({country_revenue}, 0)"
  type: number
```

### Example

Data model:

```yaml
cubes:
  - name: percent_of_total
    sql: |
      SELECT 1 AS id, 1000 AS revenue, 'A' AS product, 'USA' AS country UNION ALL
      SELECT 2 AS id, 2000 AS revenue, 'B' AS product, 'USA' AS country UNION ALL
      SELECT 3 AS id, 3000 AS revenue, 'A' AS product, 'Austria' AS country UNION ALL
      SELECT 4 AS id, 4000 AS revenue, 'B' AS product, 'Austria' AS country UNION ALL
      SELECT 5 AS id, 5000 AS revenue, 'A' AS product, 'Netherlands' AS country UNION ALL
      SELECT 6 AS id, 6000 AS revenue, 'B' AS product, 'Netherlands' AS country

    dimensions:
      - name: product
        sql: product
        type: string

      - name: country
        sql: country
        type: string

    measures:
      - name: revenue
        sql: revenue
        format: currency
        type: sum
      
      - name: country_revenue
        multi_stage: true
        sql: "{revenue}"
        format: currency
        type: sum
        group_by:
          - country
      
      - name: country_revenue_percentage
        multi_stage: true
        sql: "{revenue} / NULLIF({country_revenue}, 0)"
        type: number
```

Query and result:

<Screenshot src="https://ucarecdn.com/0d0b318c-0743-4698-bb05-b9bf6dfeb292/" />

## Nested aggregate

Nested aggregate calculations are used to compute an aggregate of an aggregate, e.g.,
to calculate the average of per-customer averages or to count how many customers exceed
a threshold. Use the [`add_group_by` parameter][ref-add-group-by] of a multi-stage
measure to specify dimensions for the inner aggregation stage.

For example, calculating the average order value per customer, then averaging across
customers:

```yaml
- name: avg_order_value
  sql: amount
  type: avg

- name: avg_customer_order_value
  multi_stage: true
  sql: "{avg_order_value}"
  type: avg
  add_group_by:
    - customer_id
```

Counting customers with total spending above a threshold:

```yaml
- name: total_amount
  sql: amount
  type: sum

- name: high_value_customer_count
  multi_stage: true
  sql: "CASE WHEN {total_amount} > 1000 THEN 1 END"
  type: count
  add_group_by:
    - customer_id
```

### Example

Data model:

```yaml
cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, 100 AS amount, 1 AS customer_id, 'USA' AS country UNION ALL
      SELECT 2 AS id, 150 AS amount, 1 AS customer_id, 'USA' AS country UNION ALL
      SELECT 3 AS id, 200 AS amount, 2 AS customer_id, 'USA' AS country UNION ALL
      SELECT 4 AS id, 300 AS amount, 2 AS customer_id, 'USA' AS country UNION ALL
      SELECT 5 AS id, 400 AS amount, 2 AS customer_id, 'USA' AS country UNION ALL
      SELECT 6 AS id, 500 AS amount, 3 AS customer_id, 'Germany' AS country UNION ALL
      SELECT 7 AS id, 600 AS amount, 3 AS customer_id, 'Germany' AS country UNION ALL
      SELECT 8 AS id, 250 AS amount, 4 AS customer_id, 'Germany' AS country

    dimensions:
      - name: customer_id
        sql: customer_id
        type: number

      - name: country
        sql: country
        type: string

    measures:
      - name: avg_order_value
        sql: amount
        type: avg

      - name: avg_customer_order_value
        multi_stage: true
        sql: "{avg_order_value}"
        type: avg
        add_group_by:
          - customer_id
```

When querying `avg_customer_order_value` grouped by `country`, Cube computes the average
order value per customer first (inner stage), then averages those values per country
(outer stage). This gives equal weight to each customer regardless of order count.

## Ranking

Ranking calculations can be used to get valuable insights, especially when analyzing
data across various dimensions. Use the [`reduce_by` parameter][ref-reduce-by] of a
multi-stage measure to specify dimensions to exclude from the inner aggregation stage.

```yaml
- name: product_rank
  multi_stage: true
  order_by:
    - sql: "{revenue}"
      dir: asc
  reduce_by: 
    - product
  type: rank
```

You can reduce by one or more dimensions.

### Example

Data model:

```yaml
cubes:
  - name: ranking
    sql: |
      SELECT 1 AS id, 1000 AS revenue, 'A' AS product, 'USA' AS country UNION ALL
      SELECT 2 AS id, 2000 AS revenue, 'B' AS product, 'USA' AS country UNION ALL
      SELECT 3 AS id, 3000 AS revenue, 'A' AS product, 'Austria' AS country UNION ALL
      SELECT 4 AS id, 4000 AS revenue, 'B' AS product, 'Austria' AS country UNION ALL
      SELECT 5 AS id, 5000 AS revenue, 'A' AS product, 'Netherlands' AS country UNION ALL
      SELECT 6 AS id, 6000 AS revenue, 'B' AS product, 'Netherlands' AS country

    dimensions:
      - name: product
        sql: product
        type: string

      - name: country
        sql: country
        type: string

    measures:
      - name: revenue
        sql: revenue
        format: currency
        type: sum

      - name: product_rank
        multi_stage: true
        order_by:
          - sql: "{revenue}"
            dir: asc
        reduce_by: 
          - product
        type: rank
```

Query and result:

<Screenshot src="https://ucarecdn.com/a5e5b94e-d2a6-4c1f-a8fa-58e7dfaf4d31/" />


[link-tesseract]: https://cube.dev/blog/introducing-next-generation-data-modeling-engine
[ref-measures]: /product/data-modeling/concepts#measures
[ref-dimensions]: /product/data-modeling/concepts#dimensions
[ref-rolling-window]: /product/data-modeling/reference/measures#rolling_window
[link-cte]: https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression
[ref-ref-time-shift]: /product/data-modeling/reference/measures#time_shift
[ref-calendar-cubes]: /product/data-modeling/concepts/calendar-cubes
[ref-case-measures]: /product/data-modeling/reference/measures#case
[ref-switch-dimensions]: /product/data-modeling/reference/types-and-formats#switch
[ref-group-by]: /product/data-modeling/reference/measures#group_by
[ref-reduce-by]: /product/data-modeling/reference/measures#reduce_by
[ref-add-group-by]: /product/data-modeling/reference/measures#add_group_by