RollingSum

Time intelligence

RollingSum(expr, n, grain)

A rolling window of n grain-periods ("day" | "week" | "month" | "quarter" | "year") ending at each bucket. Missing periods count as zero, not skipped — the window runs over a gap-complete series of buckets, so a month with no sales still advances the window. Additivity rules match YTD (SQX015).

When to use it

Smoothing seasonality: trailing-12-month revenue is the way to see trend through year-end spikes.
Trailing activity windows — orders in the last 4 weeks, signups in the last 90 days.

Example

Revenue12mRolling := RollingSum([Revenue], 12, "month")

Under the hood

The measure’s history computes once in a values CTE; a range self-join then sums each bucket’s trailing window — gap-correct by construction:

SquareX
Compiled SQL (simplified)

// On a line chart by month
Revenue12mRolling := RollingSum([Revenue], 12, "month")

WITH "__sqx_0" AS (
  -- full month-grain history (filters on the rolling axis are kept
  -- on the OUTER query so the look-back window isn't cut short)
  SELECT DATE_TRUNC('month', "Sales"."sold_at") AS "kb",
         SUM("Sales"."amount") AS "val"
  FROM "sales" AS "Sales"
  GROUP BY 1
),
"__sqx_0_roll" AS (
  SELECT a."kb", SUM(b."val") AS "val"
  FROM "__sqx_0" AS a
  JOIN "__sqx_0" AS b
    ON b."kb" >  a."kb" - INTERVAL '12 months'
   AND b."kb" <= a."kb"
  GROUP BY a."kb"
)
SELECT DATE_TRUNC('month', "Sales"."sold_at") AS "month",
       MAX("__sqx_0_roll"."val") AS "Revenue12mRolling"
FROM "sales" AS "Sales"
LEFT JOIN "__sqx_0_roll"
  ON "__sqx_0_roll"."kb" = DATE_TRUNC('month', "Sales"."sold_at")
GROUP BY 1

A month with no sales contributes nothing to any window it falls in — the zero rule, with no date-spine table required.