MOVINGAVG
Category: Rollforward & time series
Overview
The MOVINGAVG function returns the moving average over the last WindowSize elements including the current one. If there are not enough preceding elements to fill the window, null is returned for those entries.
Use this function when you need a rolling average over a defined time window.
Syntax
MOVINGAVG('Node', "TimeLevel", WindowSize)
Example usage: MOVINGAVG('Revenue', "Month", 3)
Parameters
| Parameter | Description | Type | Required |
|---|---|---|---|
| Node | Input node, specified using the node name in single quotes (e.g.'Revenue') | Node reference | Yes |
| TimeLevel | Name of the time level that forms the time series to average (e.g."Month", "Quarter") | Level name | Yes |
| WindowSize | Number of elements (including the current one) to average. Must be greater than 0. | Number | Yes |
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Same as input, but the time dimension is at the specified TimeLevel granularity. If the input is finer than the specified level, it is automatically rolled up. |
| Time range | The first (WindowSize - 1) entries are dropped because there are not enough preceding values to fill the window. |
| Values | Each value is the average of the current and the preceding (WindowSize - 1) entries along the time level. |
| Row count | Reduced by (WindowSize - 1) entries compared to the input. |
Watch Out
- The first (WindowSize - 1) entries are always null (dropped) because the window cannot be fully filled.
- The input must have a time level. If the input has no time dimension, the function fails.
- The input must be at least as fine-grained as the specified time level. For example, you cannot compute a moving average on
"Month"if the input only has"Year"data. - If the input is finer than the specified level (e.g. input is at
"Month"but you specify"Quarter"), the data is automatically rolled up to the specified level before averaging.
Examples
Two-period moving average by quarter
This example shows a two-period moving average across quarterly values. The result starts once enough values are available to fill the window.
Input node: Profit
| Quarter | Profit |
|---|---|
| 2025-Q1 | 30 |
| 2025-Q2 | 120 |
| 2025-Q3 | 210 |
| 2025-Q4 | 300 |
Formula: MOVINGAVG('Profit', "Quarter", 2)
| Quarter | → MOVINGAVG Result |
|---|---|
| 2025-Q2 | (30 + 120) / 2 = 75 |
| 2025-Q3 | (120 + 210) / 2 = 165 |
| 2025-Q4 | (210 + 300) / 2 = 255 |
2025-Q1 has no result because there is no preceding quarter to complete the two-element window.
Three-period moving average with additional dimensions
MOVINGAVG computes the window independently for each combination of non-time dimensions.
Input node: Sales
| Month | Region | Value |
|---|---|---|
| 2025-01 | EMEA | 100 |
| 2025-02 | EMEA | 200 |
| 2025-03 | EMEA | 150 |
| 2025-01 | APAC | 50 |
| 2025-02 | APAC | 80 |
| 2025-03 | APAC | 110 |
Formula: MOVINGAVG('Sales', "Month", 3)
| Month | Region | -> MOVINGAVG Result |
|---|---|---|
| 2025-03 | EMEA | (100 + 200 + 150) / 3 = 150 |
| 2025-03 | APAC | (50 + 80 + 110) / 3 = 80 |
With a window of 3, only the third month has enough preceding values. Each region is averaged independently.
Related Functions
| Function | When to use instead |
|---|---|
| MOVINGSUM | When you need a rolling sum over the same time window instead of an average. |
| SHIFT | When you need to shift values along a time level rather than aggregate them over a rolling window. |