Multi-stage calculations

Measures are usually calculated as aggregations over 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 (CTEs) in the generated SQL query.

Multi-stage calculations are powered by Tesseract, the next-generation data modeling engine . Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.

Multi-stage calculations are not currently accelerated by pre-aggregations. Please track this issue .

Common uses of multi-stage calculations:

Rolling window, e.g., cumulative counts or moving averages.
Time-shift, e.g., year-over-year sales growth.
Period-to-date, e.g., year-to-date (YTD) analysis.
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, e.g., percent of total — use the group_by parameter to group by only the listed dimensions.
Nested aggregate, e.g., average of per-customer averages — use the add_group_by parameter to group by query dimensions plus listed.
Ranking, e.g., ranking products by revenue — use the 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 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.

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.

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:


 
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:

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


- 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 to customize how time-shifting works, e.g., to shift the time dimension to the prior date in a retail calendar.

Example

Data model:


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:

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.


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


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:

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 and used together with switch dimensions.


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


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


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`
    }
  }
})

Query and result:

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 of a multi-stage measure to specify dimensions for the inner aggregation stage.

For example, comparing revenue sales to the overall average:


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


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


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:

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


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


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


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 of a multi-stage measure to specify dimensions to exclude from the inner aggregation stage.


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


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:

Was this page useful?