ContextQMD
Back to Cube

Configurable rolling windows and time shifts

docs-mintlify/recipes/data-modeling/dynamic-rolling-windows.mdx

1.7.215.4 KB
Original Source

Use case

Sometimes you want a measure to behave dynamically: its rolling window — and the prior-period shift you compare it against — should be chosen by the data consumer at query time rather than fixed in the data model. A common example is an embedded dashboard with a "window" dropdown (R3 / R6 / R9 / R12) where picking a value should change the query, not the data model.

The window and the shift can't be passed as query parameters. Both rolling_window and time_shift are properties of a measure definition, resolved when the model compiles — and a member must mean the same thing in every query, otherwise caching, pre-aggregation matching, and governance break.

The trick is to move the choice into the query instead. A switch dimension holds the set of allowed windows and acts as the query-time parameter, and case measures dispatch to the matching rolling logic based on the selected value. Consumers only ever touch a small, fixed set of members, so those members stay well-defined and cacheable.

Data modeling

Say you have monthly gross_sales and want trailing 3-, 6-, 9-, and 12-month totals, each compared to the immediately preceding window of the same length.

The model has four parts:

  • A growth_window switch dimension whose values are the selectable windows. This is the query-time parameter.
  • A rolling_window measure per window (the current period).
  • A time_shift measure per window that shifts the current-period measure back by the window's length (the prior period).
  • Four case measures — gross_sales_current, gross_sales_prior, gross_sales_change, and gross_sales_growth_percentage — that dispatch on growth_window. Consumers query only these four, regardless of the selected window.

The per-window measures are near-identical, so Jinja generates them from a single list. Adding a window (say, R18) is a one-token change to windows, not a new measure by hand.

<Warning>

switch dimensions and case measures are powered by Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default.

</Warning> <CodeGroup>
yaml
{%- set windows = [3, 6, 9, 12] -%}

cubes:
  - name: gross_sales
    sql: |
      SELECT '2023-01-01'::TIMESTAMP AS month, 100 AS amount UNION ALL
      SELECT '2023-02-01'::TIMESTAMP AS month, 110 AS amount UNION ALL
      SELECT '2023-03-01'::TIMESTAMP AS month, 120 AS amount UNION ALL
      SELECT '2023-04-01'::TIMESTAMP AS month, 130 AS amount UNION ALL
      SELECT '2023-05-01'::TIMESTAMP AS month, 140 AS amount UNION ALL
      SELECT '2023-06-01'::TIMESTAMP AS month, 150 AS amount UNION ALL
      SELECT '2023-07-01'::TIMESTAMP AS month, 160 AS amount UNION ALL
      SELECT '2023-08-01'::TIMESTAMP AS month, 170 AS amount UNION ALL
      SELECT '2023-09-01'::TIMESTAMP AS month, 180 AS amount UNION ALL
      SELECT '2023-10-01'::TIMESTAMP AS month, 190 AS amount UNION ALL
      SELECT '2023-11-01'::TIMESTAMP AS month, 200 AS amount UNION ALL
      SELECT '2023-12-01'::TIMESTAMP AS month, 210 AS amount UNION ALL
      SELECT '2024-01-01'::TIMESTAMP AS month, 220 AS amount UNION ALL
      SELECT '2024-02-01'::TIMESTAMP AS month, 230 AS amount UNION ALL
      SELECT '2024-03-01'::TIMESTAMP AS month, 240 AS amount UNION ALL
      SELECT '2024-04-01'::TIMESTAMP AS month, 250 AS amount UNION ALL
      SELECT '2024-05-01'::TIMESTAMP AS month, 260 AS amount UNION ALL
      SELECT '2024-06-01'::TIMESTAMP AS month, 270 AS amount UNION ALL
      SELECT '2024-07-01'::TIMESTAMP AS month, 280 AS amount UNION ALL
      SELECT '2024-08-01'::TIMESTAMP AS month, 290 AS amount UNION ALL
      SELECT '2024-09-01'::TIMESTAMP AS month, 300 AS amount UNION ALL
      SELECT '2024-10-01'::TIMESTAMP AS month, 310 AS amount UNION ALL
      SELECT '2024-11-01'::TIMESTAMP AS month, 320 AS amount UNION ALL
      SELECT '2024-12-01'::TIMESTAMP AS month, 330 AS amount

    dimensions:
      - name: month
        sql: month
        type: time
        primary_key: true

      # The query-time parameter: the selected value chooses the window.
      - name: growth_window
        type: switch
        values:
        {%- for months in windows %}
          - {{ months }}m
        {%- endfor %}

    measures:
      - name: gross_sales
        sql: amount
        type: sum

      # Trailing (current-period) totals, one per window.
      {%- for months in windows %}
      - name: r{{ months }}_gross_sales
        sql: amount
        type: sum
        rolling_window:
          trailing: {{ months }} month
      {% endfor %}

      # Prior-period counterparts, each shifted back by the window's length.
      {%- for months in windows %}
      - name: prev_r{{ months }}_gross_sales
        multi_stage: true
        sql: "{r{{ months }}_gross_sales}"
        type: number
        time_shift:
          - interval: {{ months }} month
            type: prior
      {% endfor %}

      # The members consumers query, dispatching on growth_window.
      - name: gross_sales_current
        multi_stage: true
        case:
          switch: "{CUBE.growth_window}"
          when:
          {%- for months in windows %}
            - value: {{ months }}m
              sql: "{CUBE.r{{ months }}_gross_sales}"
          {%- endfor %}
          else:
            sql: "{CUBE.r{{ windows[0] }}_gross_sales}"
        type: number

      - name: gross_sales_prior
        multi_stage: true
        case:
          switch: "{CUBE.growth_window}"
          when:
          {%- for months in windows %}
            - value: {{ months }}m
              sql: "{CUBE.prev_r{{ months }}_gross_sales}"
          {%- endfor %}
          else:
            sql: "{CUBE.prev_r{{ windows[0] }}_gross_sales}"
        type: number

      - name: gross_sales_change
        multi_stage: true
        sql: "{gross_sales_current} - {gross_sales_prior}"
        type: number

      - name: gross_sales_growth_percentage
        multi_stage: true
        sql: "100.0 * ({gross_sales_current} - {gross_sales_prior}) / NULLIF({gross_sales_prior}, 0)"
        type: number
javascript
const windows = [3, 6, 9, 12];

cube(`gross_sales`, {
  sql: `
    SELECT '2023-01-01'::TIMESTAMP AS month, 100 AS amount UNION ALL
    SELECT '2023-02-01'::TIMESTAMP AS month, 110 AS amount UNION ALL
    SELECT '2023-03-01'::TIMESTAMP AS month, 120 AS amount UNION ALL
    SELECT '2023-04-01'::TIMESTAMP AS month, 130 AS amount UNION ALL
    SELECT '2023-05-01'::TIMESTAMP AS month, 140 AS amount UNION ALL
    SELECT '2023-06-01'::TIMESTAMP AS month, 150 AS amount UNION ALL
    SELECT '2023-07-01'::TIMESTAMP AS month, 160 AS amount UNION ALL
    SELECT '2023-08-01'::TIMESTAMP AS month, 170 AS amount UNION ALL
    SELECT '2023-09-01'::TIMESTAMP AS month, 180 AS amount UNION ALL
    SELECT '2023-10-01'::TIMESTAMP AS month, 190 AS amount UNION ALL
    SELECT '2023-11-01'::TIMESTAMP AS month, 200 AS amount UNION ALL
    SELECT '2023-12-01'::TIMESTAMP AS month, 210 AS amount UNION ALL
    SELECT '2024-01-01'::TIMESTAMP AS month, 220 AS amount UNION ALL
    SELECT '2024-02-01'::TIMESTAMP AS month, 230 AS amount UNION ALL
    SELECT '2024-03-01'::TIMESTAMP AS month, 240 AS amount UNION ALL
    SELECT '2024-04-01'::TIMESTAMP AS month, 250 AS amount UNION ALL
    SELECT '2024-05-01'::TIMESTAMP AS month, 260 AS amount UNION ALL
    SELECT '2024-06-01'::TIMESTAMP AS month, 270 AS amount UNION ALL
    SELECT '2024-07-01'::TIMESTAMP AS month, 280 AS amount UNION ALL
    SELECT '2024-08-01'::TIMESTAMP AS month, 290 AS amount UNION ALL
    SELECT '2024-09-01'::TIMESTAMP AS month, 300 AS amount UNION ALL
    SELECT '2024-10-01'::TIMESTAMP AS month, 310 AS amount UNION ALL
    SELECT '2024-11-01'::TIMESTAMP AS month, 320 AS amount UNION ALL
    SELECT '2024-12-01'::TIMESTAMP AS month, 330 AS amount
  `,

  dimensions: {
    month: {
      sql: `month`,
      type: `time`,
      primary_key: true
    },

    // The query-time parameter: the selected value chooses the window.
    growth_window: {
      type: `switch`,
      values: windows.map(months => `${months}m`)
    }
  },

  measures: {
    gross_sales: {
      sql: `amount`,
      type: `sum`
    },

    // Trailing (current-period) totals and their prior-period counterparts,
    // one pair per window.
    ...windows.reduce((members, months) => {
      const current = `r${months}_gross_sales`;
      const prior = `prev_r${months}_gross_sales`;
      return {
        ...members,

        [current]: {
          sql: `amount`,
          type: `sum`,
          rolling_window: {
            trailing: `${months} month`
          }
        },

        [prior]: {
          multi_stage: true,
          sql: `${CUBE[current]}`,
          type: `number`,
          time_shift: [{
            interval: `${months} month`,
            type: `prior`
          }]
        }
      };
    }, {}),

    // The members consumers query, dispatching on growth_window.
    gross_sales_current: {
      multi_stage: true,
      case: {
        switch: `${CUBE.growth_window}`,
        when: windows.map(months => {
          const current = `r${months}_gross_sales`;
          return { value: `${months}m`, sql: `${CUBE[current]}` };
        }),
        else: {
          sql: `${CUBE[`r${windows[0]}_gross_sales`]}`
        }
      },
      type: `number`
    },

    gross_sales_prior: {
      multi_stage: true,
      case: {
        switch: `${CUBE.growth_window}`,
        when: windows.map(months => {
          const prior = `prev_r${months}_gross_sales`;
          return { value: `${months}m`, sql: `${CUBE[prior]}` };
        }),
        else: {
          sql: `${CUBE[`prev_r${windows[0]}_gross_sales`]}`
        }
      },
      type: `number`
    },

    gross_sales_change: {
      multi_stage: true,
      sql: `${gross_sales_current} - ${gross_sales_prior}`,
      type: `number`
    },

    gross_sales_growth_percentage: {
      multi_stage: true,
      sql: `100.0 * (${gross_sales_current} - ${gross_sales_prior}) / NULLIF(${gross_sales_prior}, 0)`,
      type: `number`
    }
  }
});
</CodeGroup> <Note>

Two requirements make case measures work:

  • Every case measure needs an else branch. It provides the value when the selected switch value matches no when clause.
  • Always include the switch dimension (growth_window) in the query. The case measures — and the calculated measures built on top of them — need it to dispatch. To pin a single window, add a filter on it (see below); don't rely on the filter alone.
</Note>

To give consumers a sensible default window, expose the cube through a view with a default_filters entry on growth_window. The unless clause releases the default as soon as the consumer filters on growth_window explicitly, so a query with no window filter gets the default, and a query that picks a window gets that one:

<CodeGroup>
yaml
views:
  - name: gross_sales_view
    cubes:
      - join_path: gross_sales
        includes: "*"

    default_filters:
      - member: gross_sales.growth_window
        operator: equals
        values:
          - 3m
        unless:
          - gross_sales.growth_window
javascript
view(`gross_sales_view`, {
  cubes: [{
    join_path: gross_sales,
    includes: `*`
  }],

  defaultFilters: [{
    member: `gross_sales.growth_window`,
    operator: `equals`,
    values: [`3m`],
    unless: [`gross_sales.growth_window`]
  }]
});
</CodeGroup>

Result

Query the view through the SQL API, selecting growth_window and the four consumer measures. Wrap measures in MEASURE() and provide a date range for the rolling windows.

With no filter on growth_window, the default_filters entry applies the default window (3m):

sql
SELECT
  growth_window,
  MEASURE(gross_sales_current),
  MEASURE(gross_sales_prior),
  MEASURE(gross_sales_change),
  MEASURE(gross_sales_growth_percentage)
FROM gross_sales_view
WHERE month >= '2024-12-01' AND month < '2025-01-01'
GROUP BY 1;
growth_windowgross_sales_currentgross_sales_priorgross_sales_changegross_sales_growth_percentage
3m9608709010.34

Filtering on growth_window — what a "window" dropdown does — selects that window:

sql
SELECT
  growth_window,
  MEASURE(gross_sales_current),
  MEASURE(gross_sales_prior),
  MEASURE(gross_sales_change),
  MEASURE(gross_sales_growth_percentage)
FROM gross_sales_view
WHERE month >= '2024-12-01' AND month < '2025-01-01'
  AND growth_window = '9m'
GROUP BY 1;
growth_windowgross_sales_currentgross_sales_priorgross_sales_changegross_sales_growth_percentage
9m2610180081045

Filtering on all values returns every window side by side, e.g. to render a comparison:

sql
SELECT
  growth_window,
  MEASURE(gross_sales_current),
  MEASURE(gross_sales_change)
FROM gross_sales_view
WHERE month >= '2024-12-01' AND month < '2025-01-01'
  AND growth_window IN ('3m', '6m', '9m', '12m')
GROUP BY 1
ORDER BY 1;
growth_windowgross_sales_currentgross_sales_change
3m96090
6m1830360
9m2610810
12m33001440
  • If you need a single fixed window rather than a consumer-selectable one, see Active users (DAU, WAU, MAU) (fixed rolling_window measures) and Period-over-period changes (a fixed time_shift comparison). This recipe generalizes both, making the window and shift selectable at query time.
  • Passing dynamic parameters in a query also lets a consumer choose something at query time, but the choice there is a data value (e.g. a city) injected into a calculation — not a measure behavior (the window length) as it is here.
  • Generating the data model dynamically generates a family of members from a list at model-build time; the consumer then picks by choosing which member to query, rather than passing a query-time value.