Moving Average

Moving averages smooth out fluctuations in a time series so the underlying trend is easier to see. This page builds a 3-month moving average of monthly revenue two ways: with trailing_period() and with window_avg(). It explains when to pick which.

Uses the shared e-commerce schema: orders, order_items, products.

Which function to use?Copy Anchor Link

For example, with gaps in the data:

trailing_period divides Mar's revenue by 3 because Feb and Apr are empty months. window_avg averages the three visible rows regardless of how far apart they are.

Default to trailing_period() for time-based moving averages. Reach for window_avg() when you genuinely want row-based logic (e.g. last-3-orders rather than last-3-months).

SetupCopy Anchor Link

Start with a plain revenue metric on the e-commerce dataset:

Dataset e_commerce {
  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql order_items | sum(order_items.quantity * products.price) ;;
  }
}

Option 1: trailing_period()Copy Anchor Link

trailing_period() re-aggregates revenue over a moving calendar window. Two things to decide:

• The time dimension: the field that defines "which period a row belongs to." Use orders.created_at (no need to convert to month grain; the interval argument handles that).
• The window: interval(3 months) for a 3-month trailing window.

Because trailing_period() returns the sum over the window, divide by 3 to get the average:

metric moving_avg_revenue_3m_tp {
  label: '3M Moving Avg. Revenue (trailing_period)'
  type: 'number'
  definition: @aql trailing_period(revenue, orders.created_at, interval(3 months)) / 3 ;;
}

The first two rows look low because only 1 and 2 months of revenue exist within their windows. Filter them out in the visualization if that's confusing.

Option 2: window_avg()Copy Anchor Link

window_avg() averages adjacent rows. You need to tell it:

• The frame: -2..0 means "from 2 rows back through the current row" (3 rows total).
• The order: orders.created_at | month() so rows are ordered by month before the window is applied.

metric moving_avg_revenue_3m_wa {
  label: '3M Moving Avg. Revenue (window_avg)'
  type: 'number'
  definition: @aql window_avg(revenue, -2..0, order: orders.created_at | month()) ;;
}

The first two rows average 1 and 2 rows respectively. To NULL them out instead, gate on window_count:

metric moving_avg_revenue_3m_wa {
  label: '3M Moving Avg. Revenue (window_avg)'
  type: 'number'
  definition: @aql case(
    when: window_count(revenue, -2..0, order: orders.created_at | month()) < 3,
    then: null,
    else: window_avg(revenue, -2..0, order: orders.created_at | month())
  ) ;;
}

Read the resultCopy Anchor Link

Query revenue and the moving average side by side, one row per month. The raw series swings month to month; the smoothed column moves slowly, so the trend is easy to read even in a plain table.

See alsoCopy Anchor Link

• trailing_period: function reference
• window_avg: function reference
• Cumulative Metrics: running totals and accumulation
• Period Comparison: YoY, QoQ, and CAGR