Skip to main content
Last updated on

Advanced techniques for metric views

Advanced techniques for metric views enable sophisticated calculations such as moving averages, period-over-period changes, running totals, and complex derived KPIs while maintaining consistency and reusability across your semantic layer. This page explains how to use window measures for time-series analysis and composability for building complex metrics from simpler measures.

This page assumes familiarity with basic metric view modeling concepts. See Model metric views.

note

The examples on this page use the TPC-H sample dataset, which models a wholesale supply chain. For more information about the TPC-H dataset, see tpch. For an end-to-end tutorial using this dataset with metric views, see Tutorial: Build a complete metric view with joins.

Window measures​

Experimental

This feature is Experimental.

Window measures enable you to define measures with windowed, cumulative, or semi-additive aggregations in your metric views, supporting calculations such as moving averages, period-over-period changes, and running totals. This section includes practical examples.

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. This does not include the current unit.
    • all: Includes all rows regardless of the window value.

  • semiadditive: Specifies how to aggregate 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 rolling 7-day count of unique customers who placed orders. This metric helps track customer engagement trends over time by showing how many distinct customers made purchases in the week leading up to each date.

YAML
version: 1.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 when date isn't a grouping column.

Period-over-period window measure example​

The following example calculates day-over-day sales growth by comparing today's revenue (sum of all order prices) to yesterday's revenue. This metric helps identify daily sales trends and shows the percentage change in revenue, which is useful for monitoring business performance and spotting anomalies.

YAML
version: 1.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​

The following example calculates cumulative sales revenue from the beginning of the dataset up to each date. This running total shows how much total revenue has been generated over time, useful for tracking progress toward annual revenue goals or analyzing long-term growth patterns.

YAML
version: 1.1
source: samples.tpch.orders

filter: o_orderdate > DATE'1998-01-01'

dimensions:
  - name: date
    expr: o_orderdate
  - name: customer
    expr: o_custkey

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: Specifies the dimension that defines the ordering of records within the window. In this example, date orders the window chronologically.

  • range: cumulative defines the window as all data from the beginning of the dataset up to and including each date.

  • semiadditive: last controls how the measure is aggregated when date is not included in the query. When the date dimension is not included in your query's GROUP BY, the measure returns the last (most recent) cumulative value rather than summing across all dates.

Period to date measure example​

The following example calculates year-to-date (YTD) sales revenue. This measure shows the cumulative revenue generated from January 1st of each year up to the current date, resetting at the beginning of each new year. This metric is essential for comparing current year performance to prior years and tracking progress toward annual targets.

YAML
version: 1.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 year dimension restricts the cumulative sum to calculate it only within the current year.

Semiadditive measure example​

The following example calculates account balances, which should not be summed across dates (you can't add Monday's balance to Tuesday's balance to get total balance). Instead, when aggregating across multiple days, the measure returns the most recent balance. However, the measure can still be summed across customers to show total balance across all accounts on a given day.

YAML
version: 1.1

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: Specifies the dimension that defines the ordering of records within the window. In this example, date orders the window chronologically.

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

  • semiadditive: last returns the most recent balance when aggregating over multiple days.

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_customers) as m
FROM my_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL

Composability​

Metric views are composable. You can build new dimensions and measures that reference existing ones rather than rewriting logic from scratch. This reduces duplication and makes complex metric definitions easier to maintain.

Composability works at two levels: within a single metric view, and across metric views. A metric view can use another metric view as its source, allowing you to layer definitions and build progressively richer semantic models without duplicating logic.

Composability supports the following reference patterns:

  • Earlier dimensions in new dimensions.
  • Dimensions and earlier measures in new measures.
  • Dimensions from metric views used as source in new dimensions.
  • Dimensions and measures from metric views used as source in new measures.

Define measures with composability​

In the measures section, you can reference measures from the source metric view or measures defined earlier in the same metric view. This approach improves consistency, auditability, and maintenance of your semantic layer.

Measure type

Description

Example

Atomic

A simple, direct aggregation on a source column. These form the building blocks.

SUM(o_totalprice)

Composed

An expression that mathematically combines one or more other measures using the MEASURE() function.

MEASURE(total_revenue) / MEASURE(order_count)

Example: Average Order Value (AOV)​

The following example defines Average Order Value (AOV) using two atomic measures: total_revenue (sum of order prices) and order_count (number of orders). The avg_order_value measure references both atomic measures.

YAML
version: 1.1

source: samples.tpch.orders

measures:
  # Total Revenue
  - name: total_revenue
    expr: SUM(o_totalprice)

  # Order Count
  - name: order_count
    expr: COUNT(1)

  # Composed Measure: Average Order Value (AOV)
  - name: avg_order_value
    # Defines AOV as Total Revenue divided by Order Count
    expr: MEASURE(total_revenue) / MEASURE(order_count)

If the total_revenue definition changes (for example, to exclude tax), avg_order_value automatically uses the updated definition.

Composability with conditional logic​

You can use composability to create complex ratios, conditional percentages, and growth rates without relying on window functions for simple period-over-period calculations.

Example: Fulfillment Rate​

The following example calculates fulfillment rate: the percentage of orders with status 'F' (fulfilled). The measure divides fulfilled orders by total orders.

YAML
version: 1.1

source: samples.tpch.orders

measures:
  # Total Orders (denominator)
  - name: total_orders
    expr: COUNT(1)

  # Fulfilled Orders (numerator)
  - name: fulfilled_orders
    expr: COUNT(1) FILTER (WHERE o_orderstatus = 'F')

  # Composed Measure: Fulfillment Rate (Ratio)
  - name: fulfillment_rate
    expr: MEASURE(fulfilled_orders) / MEASURE(total_orders)
    format:
      type: percentage

Best practices for composability​

  1. Define atomic measures first: Establish fundamental measures (SUM, COUNT, AVG) before defining measures that reference them.
  2. Use MEASURE() for references: Use the MEASURE() function when referencing another measure in an expr. Don't repeat aggregation logic manually. For example, avoid SUM(a) / COUNT(b) if measures for both values already exist.
  3. Prioritize readability: Compose measures using clear mathematical formulas. For example, MEASURE(gross_profit) / MEASURE(total_revenue) is clearer than a single complex SQL expression.
  4. Add semantic metadata: Use semantic metadata to format composed measures (for example, percentages or currency) for downstream tools. See Agent metadata in metric views.

Next steps​