MOVINGAVG
MOVINGAVG('Node', "TimeLevel", WindowSize)
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: MOVINGAVG('Revenue', "Month", 3).
Parameters
- NodeNode referenceRequired
- Input node, specified using the node name in single quotes (e.g.
'Revenue') - TimeLevelLevel nameRequired
- Name of the time level that forms the time series to average (e.g.
"Month","Quarter") - WindowSizeNumberRequired
- Number of elements (including the current one) to average. Must be greater than 0.
Output shape
- 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 | Calculation | 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 | Calculation | 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.