Aggregate to Period

Quick start: quarterly payroll changes

jobs = DIFF(SERIES_SINCE(PAYEMS,20000101))
quarterly = AGGREGATE_TO_PERIOD(jobs,QUARTERLY,SUM)
LINE_CHART(SET_NAME(quarterly,"Quarterly net payroll employment change"))

Run this example in RainbowStats

DIFF(PAYEMS) produces monthly changes in payroll employment. Summing these changes within each calendar quarter gives the net change represented by the available monthly observations. The resulting values remain in thousands of persons.

Syntax, periods and aliases

AGGREGATE_TO_PERIOD(series,period,method)

Supply all three arguments. The command returns a data series that can be charted, transformed or used in a model. The supplied dispatcher accepts a single series, not a data-series list.

AGGREGATE_TO_PERIOD(UNRATE,QUARTERLY,MEAN)
AGGREGATE(UNRATE,QUARTERLY,MEAN)
AGGREGATETOPERIOD(UNRATE,QUARTERLY,MEAN)
RESAMPLE(UNRATE,QUARTERLY,MEAN)

AGGREGATE, AGGREGATETOPERIOD and RESAMPLE are registered aliases for the same command.

Aggregation combines observations at the existing or a coarser frequency. It does not interpolate to a finer frequency: for example, monthly input cannot be aggregated to weekly observations.

Choose the aggregation method

One quarter, six possible answers

For three monthly values of 3.7, 3.9, 3.9, the methods give:

These values were verified using January–March 2024 unemployment observations. The average is a meaningful quarterly average unemployment rate; a sum of the rates is not a conventional quarterly unemployment measure.

MEAN weights the included observations equally. It is not automatically weighted by calendar days, trading volume, population or another exposure measure. Likewise, summing rates expressed per day or at an annual rate does not automatically convert them into a period total.

Output dates, coverage and partial periods

Each output point retains the date of the last actual observation in its calendar bucket. It is not automatically relabeled to the formal calendar-period end.

In live tests, monthly January–March data aggregated to a quarterly series were dated March 1, the last observation date in that group. FIRST returned January’s value but used that same March output date. January–June data aggregated annually were dated June 1.

Inspect the input sample and dates before interpreting the final period. Clipping the input in the middle of a quarter or year changes the observations entering that period. Recent source data may also leave the latest period incomplete.

Do not infer completeness from an output frequency label. The command interface has no minimum-observation count, completeness threshold, weighting parameter, date-label option or weekly-boundary option. Check period coverage and weekly grouping against your intended convention when those details matter.

Examples: rates, endpoints, extremes and lists

Quarterly average unemployment

x = SERIES_SINCE(UNRATE,20000101)
q = AGGREGATE_TO_PERIOD(x,QUARTERLY,MEAN)
LINE_CHART(SET_NAME(q,"Quarterly average unemployment rate"))

Monthly final observed Treasury yield

x = SERIES_SINCE(DGS10,20200101)
m = AGGREGATE_TO_PERIOD(x,MONTHLY,LAST)
LINE_CHART(SET_NAME(m,"Last observed 10-year Treasury yield each month"))

This is the final recorded yield in each month, which may precede calendar month-end because of weekends, holidays or source gaps.

Weekly average daily yield

x = SERIES_SINCE(DGS10,20200101)
w = AGGREGATE_TO_PERIOD(x,WEEKLY,MEAN)
LINE_CHART(SET_NAME(w,"Weekly average 10-year Treasury yield"))

The average reflects available daily observations. Missing holidays do not become additional calendar-day observations.

Monthly observed highs and lows

x = SERIES_SINCE(DGS10,20200101)
high = SET_NAME(AGGREGATE_TO_PERIOD(x,MONTHLY,MAX),"Monthly observed high")
low = SET_NAME(AGGREGATE_TO_PERIOD(x,MONTHLY,MIN),"Monthly observed low")
LINE_CHART(LIST(high,low))

Prepare two series at the same target frequency

u = AGGREGATE_TO_PERIOD(SERIES_SINCE(UNRATE,20000101),QUARTERLY,MEAN)
c = AGGREGATE_TO_PERIOD(SERIES_SINCE(TCU,20000101),QUARTERLY,MEAN)
r = REGRESSION(LIST(u,c))
EXTRACT(r,"slideshow")

Aggregate each series separately, then construct the list. A shared target frequency does not by itself establish identical date labels or sample coverage, especially when sources use different observation dates. Check alignment before modeling.

Order of operations changes the question

For a complete, regularly sampled series, summing all consecutive monthly absolute changes in a quarter gives the difference between its final level and the preceding quarter’s final level. That identity depends on having the needed boundary observation and all intervening changes.

When a first difference or log difference is required for the first month of a selected sample, retain the preceding month in the input history. Transform first, then choose the output range deliberately.

Working with the data and common errors

• Nonfinite observations are ignored. The aggregation method’s documented contract excludes nonfinite values and omits all-missing buckets. Missing observations are not filled with zeros. A mean therefore describes the included finite observations; check coverage before treating it as a complete-period average.
• Check units after aggregation. Tested results retain source unit labels. Use SET_NAME and SET_UNITS where the aggregation changes the intended interpretation.
• Separate regular aggregation from smoothing. These are calendar-period groups, not a rolling window of a fixed number of observations.
• Check source definitions. A monthly series may already be an average, total, endpoint or seasonally adjusted annual rate. Its construction determines which subsequent aggregation is meaningful.

DIFF · LOGDIFF · LAG