Skip to main content

Use window measures in metric views

Experimental

This feature is Experimental.

Window measures enable you to define measures with windowed, cumulative, or semiadditive aggregations in your metric views. These types of measures allow for more complex calculations, such as moving averages, period-over-period changes, and running totals. This page includes practical examples demonstrating how to work with window measures in metric views.

Define a window measure

Window measures enable defining measures with windowed, cumulative, or semi-additive aggregations. A window measure includes the following required values:

  • order: The dimension that determines the ordering of the window.

  • range: Defines the extent of the window, such as trailing, cumulative, or all data. Possible range values include the following:

    • current: Includes rows where the window ordering value equals the current row’s value.
    • cumulative: Includes all rows where the window ordering value is less than or equal to the current row’s value.
    • trailing <value> <unit>: Includes rows from the current row going backward by the specified number of time units, such as trailing 3 months. This does not include the current unit. For example, trailing 3 months excludes the current month.
    • leading <value> <unit>: Includes rows from the current row going forward by the specified number of time units, such as leading 7 days.
    • all: Includes all rows regardless of the window value.
  • semiadditive: Specifies how to summarize the measure when the order field is not included in the query's GROUP BY. Possible values include first and last.

Trailing, moving, or leading window measure example

The following example calculates a measure over a trailing or leading window of time in the last 7 days.

YAML
version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
- name: date
expr: o_orderdate

measures:
- name: t7d_customers
expr: COUNT(DISTINCT o_custkey)
window:
- order: date
range: trailing 7 day
semiadditive: last

For this example, the following configuration applies:

order: date specifies that the date dimension orders the window.

range: The trailing 7 day specification defines the window as the 7 days before each date, excluding the date itself.

semiadditive: last indicates the last value in the 7-day window is used.

Period-over-period window measure example

Calculate the change from the previous period to the current period.

YAML
version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
- name: date
expr: o_orderdate
measures:
- name: previous_day_sales
expr: SUM(o_totalprice)
window:
- order: date
range: trailing 1 day
semiadditive: last
- name: current_day_sales
expr: SUM(o_totalprice)
window:
- order: date
range: current
semiadditive: last
- name: day_over_day_growth
expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100

For this example, the following conditions apply:

  • Two window measures are used: one for calculating total sales on the previous day and one for the current day.

  • A third measure calculates the percentage change (growth) between the current and previous days.

Cumulative (running) total measure example

Calculate a running total of a measure up to each point in time.

YAML
version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'

dimensions:
- name: date
expr: o_orderdate
measures:
- name: running_total_sales
expr: SUM(o_totalprice)
window:
- order: date
range: cumulative
semiadditive: last

The following details highlight key parts of this definition:

order: date ensures that the date dimension orders the window.

range: cumulative defines the window as all data up to and including each date.

semiadditive: last makes sure that the last cumulative value is used when aggregating over dimensions.

Period to date measure example

Calculate a running total in a given period.

YAML
version: 0.1

source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'

dimensions:
- name: date
expr: o_orderdate
- name: year
expr: DATE_TRUNC('year', o_orderdate)
measures:
- name: ytd_sales
expr: SUM(o_totalprice)
window:
- order: date
range: cumulative
semiadditive: last
- order: year
range: current
semiadditive: last

The following details highlight key parts of this definition:

  • Two window measures are used: one for the cumulative sum over the date dimension and another to limit the sum to the current year.

  • The cumulative sum is restricted by the year dimension to check that it is calculated only within the current year.

Semiadditive measure example

Calculate a measure that should not be summed over a specific dimension, such as a bank balance.

YAML
dimensions:
- name: date
expr: date
- name: customer
expr: customer_id

measures:
- name: semiadditive_balance
expr: SUM(balance)
window:
- order: date
range: current
semiadditive: last

The following details highlight key parts of this definition:

  • order: date ensures the date dimension orders the window.

  • range: current restricts the window to a single day with no aggregation across days.

  • semiadditive: last ensures that the most recent balance is returned when aggregating over multiple days.

note

This window measure still sums over all customers to get the overall balance per day.

Query a window measure

You can query a metric view with a window measure like any other metric view. The following example queries a metric view:

SQL
SELECT
state,
DATE_TRUNC('month', date),
MEASURE(t7d_distinct_customers) as m
FROM sales_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL