# Moving Average

> Smooth a time series with a 3-month moving average: using trailing_period() for calendar-based windows or window_avg() for row-based windows.

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](/as-code/aql/cookbook/metrics-by-example#shared-e-commerce-schema): `orders`, `order_items`, `products`.

## Which function to use?

| | `trailing_period()` | `window_avg()` |
|---|---|---|
| **Operates on** | Calendar time | Table rows |
| **Handles gaps in data** | Yes. uses the calendar, not the rows | No. Uses adjacent rows regardless of dates |
| **Mixed time grains** (e.g. metric defined on day, viz on month) | Yes | No. viz grain must match |
| **Affected by visual filters** | No. Computed before filters | Yes. Only sees visible rows |

For example, with gaps in the data:

| Month | Revenue | `trailing_period` (3M) | `window_avg` (3 rows) |
|-------|---------|---------|----------|
| Jan   | 100     | 100     | 100      |
| Mar   | 100     | 50      | 100      |
| Jun   | 100     | 50      | 100      |

`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).

## Setup

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

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

## Option 1: `trailing_period()`

`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:

```aml
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()`

`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.

```aml
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`:

```aml
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())
  ) ;;
}
```

## Visualize

Plot revenue and the moving average together. The smoothed line makes the trend much easier to read:

## See also

- [`trailing_period`](/reference/aql/trailing_period): function reference
- [`window_avg`](/reference/aql/window_avg): function reference
- [Cumulative Metrics](/as-code/aql/cookbook/aql-cumulative-metrics): running totals and accumulation
- [Period Comparison](/as-code/aql/cookbook/aql-period-comparison): YoY, QoQ, and CAGR