MOVINGSUM
Category: Rollforward & time series
Overview
The MOVINGSUM function returns the moving sum 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 sum over a defined time window.
Syntax
MOVINGSUM('Node', "TimeLevel", WindowSize)
Example usage: MOVINGSUM('Revenue', "Month", 12)
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 sum up (e.g."Month", "Quarter") | Level name | Yes |
| WindowSize | Number of elements (including the current one) to sum up. 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 sum 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 sum 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 summing.
Examples
Two-period moving sum by quarter
This example shows a two-period moving sum 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: MOVINGSUM('Profit', "Quarter", 2)
| Quarter | → MOVINGSUM Result |
|---|---|
| 2025-Q2 | (30 + 120) = 150 |
| 2025-Q3 | (120 + 210) = 330 |
| 2025-Q4 | (210 + 300) = 510 |
2025-Q1 has no result because there is no preceding quarter to complete the two-element window.
Four-quarter rolling sum (trailing twelve months)
A common use case: summing the last 4 quarters to get a trailing twelve-month total.
Input node: Revenue
| Quarter | Value |
|---|---|
| 2025-Q1 | 100 |
| 2025-Q2 | 120 |
| 2025-Q3 | 90 |
| 2025-Q4 | 140 |
| 2026-Q1 | 110 |
| 2026-Q2 | 130 |
Formula: MOVINGSUM('Revenue', "Quarter", 4)
| Quarter | -> MOVINGSUM Result |
|---|---|
| 2025-Q4 | 100 + 120 + 90 + 140 = 450 |
| 2026-Q1 | 120 + 90 + 140 + 110 = 460 |
| 2026-Q2 | 90 + 140 + 110 + 130 = 470 |
The first three quarters have no result because the four-element window cannot be filled.
Related Functions
| Function | When to use instead |
|---|---|
| MOVINGAVG | When you need a rolling average over the same time window instead of a sum. |
| SHIFT | When you need to shift values along a time level rather than aggregate them over a rolling window. |
| RUNNINGSUM | When you need a cumulative sum from the start of the time series (no fixed window). |