PARTITION_AGGR: Creating Rolling Calculations

How to:

Reference:

Using the PARTITION_AGGR function, you can generate rolling calculations based on a block of rows from the internal matrix of a TABLE request. In order to determine the limits of the rolling calculations, you specify a partition of the data based on either a sort field or the entire TABLE. Within either type of break, you can start calculating from the beginning of the break or a number of rows prior to or subsequent to the current row. You can stop the rolling calculation at the current row, a row past the start point, or the end of the partition.

By default, the field values used in the calculations are the summed values of a measure in the request. Certain prefix operators can be used to add a column to the internal matrix and use that column in the rolling calculations. The rolling calculation can be SUM, AVE, CNT, MIN, MAX, FST, or LST.

Syntax: How to Generate Rolling Calculations Using PARTITION_AGGR 

PARTITION_AGGR([prefix.]measure,reset_key,lower,upper,operation)

where:

prefix.

Defines an aggregation operator to apply to the measure before using it in the rolling calculation. Valid operators are:

  • SUM. which calculates the sum of the measure field values. SUM is the default operator.
  • CNT. which calculates a count of the measure field values.
  • AVE. which calculates the average of the measure field values.
  • MIN. which calculates the minimum of the measure field values.
  • MAX. which calculates the maximum of the measure field values.
  • FST. which retrieves the first value of the measure field.
  • LST. which retrieves the last value of the measure field.
  • STDP. which calculates the population standard deviation.
  • STDS. which calculates the sample standard deviation.

Note: The operators PCT., RPCT., TOT., MDN., and DST. are not supported. COMPUTEs that reference those unsupported operators are also not supported.

measure

Is the measure field to be aggregated. It can be a real field in the request or a calculated value generated with the COMPUTE command, as long as the COMPUTE does not reference an unsupported prefix operator.

reset_key

Identifies the point at which the calculation restarts. Valid values are:

The sort field may use BY HIGHEST to indicate a HIGH-TO-LOW sort. ACROSS COLUMNS AND is supported. BY ROWS OVER and FOR are not supported.

lower

Identifies the starting point for the rolling calculation. Valid values are:

  • n, -n, which starts the calculation n rows forward or back from the current row.
  • B, which starts the calculation at the beginning of the current sort break (the first line with the same sort field value as the current line).
upper

Identifies the ending point of the rolling calculation. The lower row value must precede upper row value.

Valid values are:

  • C, which ends the rolling calculation at the current row in the internal matrix.
  • n, -n, which ends the calculation n rows forward or back from the current row.
  • E, which ends the rolling calculation at the end of the sort break (the last line with the same sort value as the current row.)

Note: The values used in the calculations depend on the sort sequence (ascending or descending) specified in the request. Be aware that displaying a date or time dimension in descending order may produce different results than those you may expect.

operation

Specifies the rolling calculation used on the values in the internal matrix. Supported operations are:

  • SUM, which calculates a rolling sum.
  • AVE, which calculates a rolling average.
  • CNT, which counts the rows in the partition.
  • MEDIAN.
  • MIN, which returns the minimum value in the partition.
  • MAX, which returns the maximum value in the partition.
  • MEDIAN, which returns the median value in the partition.
  • MODE, which returns the mode value in the partition.
  • FST, which returns the first value in the partition.
  • LST, which returns the last value in the partition.
  • STDP, which returns the population standard deviation in the partition. Requires using the verb PRINT to avoid duplicate aggregation.
  • STDS, which returns the sample standard deviation in the partition. Requires using the verb PRINT to avoid duplicate aggregation.

The calculation is performed prior to any WHERE TOTAL tests, but after any WHERE_GROUPED tests.

Example: Calculating a Rolling Average

The following request calculates a rolling average of the current line and the previous line in the internal matrix, within the quarter. 

TABLE FILE WF_RETAIL_LITE
SUM COGS_US 
COMPUTE AVE1/D12.2M = PARTITION_AGGR(COGS_US, TIME_QTR, -1, C, AVE);
BY BUSINESS_REGION
BY TIME_QTR
BY TIME_MTH
WHERE BUSINESS_REGION EQ 'North America' OR 'South America'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. Within each quarter, the first average is just the value from Q1, as going back 1 would cross a boundary. The second average is calculated using the first two rows within that quarter, and the third average is calculated using rows 2 and 3 within the quarter.

The following changes the rolling average to start from the beginning of the sort break. 

COMPUTE AVE1/D12.2M = PARTITION_AGGR(COGS_US, TIME_QTR ,B, C, AVE);

The output is shown in the following image. Within each quarter, the first average is just the value from Q1, as going back would cross a boundary. The second average is calculated using the first two rows within that quarter, and the third average is calculated using rows 1 through 3 within the quarter.

The following command uses the partition boundary TABLE. 

COMPUTE AVE1/D12.2M = PARTITION_AGGR(COGS_US, TABLE, B, C, AVE);

The output is shown in the following image. The rolling average keeps adding the next row to the average with no sort field break.

Reference: Usage Notes for PARTITION_AGGR

  • Fields referenced in the PARTITION_AGGR parameters but not previously mentioned in the request will not be counted in column notation or propagated to HOLD files.
  • Using the WITHIN phrase for a sum is the same as computing PARTITION_AGGR on the WITHIN sort field from B (beginning of sort break) to E (end of sort break) using SUM, as in the following example.
    TABLE FILE WF_RETAIL_LITE
SUM COGS_US WITHIN TIME_QTR AS 'WITHIN Qtr'
COMPUTE PART_WITHIN_QTR/D12.2M = PARTITION_AGGR(COGS_US, TIME_QTR, B, E, SUM); 
BY BUSINESS_REGION AS Region
BY TIME_QTR
BY TIME_MTH
WHERE BUSINESS_REGION EQ 'North America' OR 'South America'
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

    The output is shown in the following image.

    With other types of calculations, the results are not the same. For example, the following request calculates the average within quarter using the WITHIN phrase and the average within quarter using PARTITION_AGGR. 

    TABLE FILE WF_RETAIL_LITE
SUM  COGS_US AS Cost 
CNT.COGS_US AS Count AVE.COGS_US WITHIN TIME_QTR AS 'Ave Within'
COMPUTE PART_WITHIN_QTR/D12.2M = PARTITION_AGGR(COGS_US, TIME_QTR, B, E, AVE); 
BY BUSINESS_REGION AS Region
BY TIME_QTR 
ON TIME_QTR SUBTOTAL COGS_US CNT.COGS_US
BY TIME_MTH
WHERE BUSINESS_REGION EQ 'North America'
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

    The output is shown in the following image. The average using the WITHIN phrase divides the total cost for the quarter by the total count of instances for the quarter (for example, $76,829,105.00/ 252850 = $303.85), while PARTITION_AGGR divides the total cost for the quarter by the number of report rows in the quarter (for example, $76,829,105.00/3 = $25,609,701.67).

  • If you use PARTITION_AGGR to perform operations for specific time periods using an offset, for example, an operation on the quarters for different years, you must make sure that every quarter is represented. If some quarters are missing for some years, the offset will not access the correct data. In this case, generate a HOLD file that has every quarter represented for every year (you can use BY QUARTER ROWS OVER 1 OVER 2 OVER 3 OVER 4) and use PARTITION_AGGR on the HOLD file.