Rolling Window

Use the ROWS BETWEEN keyword to specify a rolling window over which running total functions (for example RUNAVG) are computed.

Running total functions such as RUNSUM, RUNAVG, RUNMIN and RUNMAX can be specified with rolling window or a shifted boundary. An example could be a moving average used with time series data to smooth out short-term fluctuations and highlight longer-term trends or cycles.

ROWS BETWEEN does not currently support the COUNT function.

Syntax

SELECT (RUNNING TOTAL)(...) ROWS BETWEEN (...) AND (...)
SELECT RUNAVG (...) ROWS BETWEEN (…) AND (…)

Examples

In the context of Date

Define a moving average

SELECT RUNAVG (Revenue) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

Return Year-to-Date (YTD) for Revenue:

SELECT RUNSUM (Revenue) WITHIN Year (Opp. Close) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Define five days preceding and the current date:

 SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 FOLLOWING

Display year to date - the same result as without specifying ROWS BETWEEN statement:

SELECT RUNSUM({metric/m}) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Define year to yesterday:

SELECT RUNSUM({metric}/m) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ROW

In the context of Month

Define the last twelve months:

SELECT RUNSUM({metric/m}) ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING

Define everything before the current month:

 SELECT RUNAVG({metric/m}) WITHIN {attribute/year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING