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.

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

Common uses of multi-stage calculations:

Period-to-date calculations, e.g., year-to-date (YTD) analysis.
Prior date calculations, e.g., year-over-year sales growth.
Fixed dimension calculations, e.g., comparing individual items to a broader dataset or calculating percent of total.
Ranking calculations.

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

Prior date

Prior date calculations can be used to find the difference between two aggregated measures, like year-over-year sales growth.

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

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:

Fixed dimension

Fixed dimension calculations can be used to perform fixed comparisons, e.g., to compare individual items to a broader dataset.

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:

Ranking

Ranking calculations can be used to get valuable insights, especially when analyzing data across various dimensions.

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

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:

Calculated members Extension