PARTITION_REF: Using Prior or Subsequent Field Values in Calculations

How to:

Reference:

Use of LAST in a calculation retrieves the LAST value of the specified field the last time this calculation was performed. In contrast, the PARTITION_REF function enables you to specify both how many rows back or forward to go in the output in order to retrieve a value, and a sort break within which the retrieval will be contained.

Syntax: How to Retrieve Prior or Subsequent Field Values for Use in a Calculation

PARTITION_REF([prefix.]field, reset_key, offset)

where:

prefix

Is optional. If used, it can be one of the following aggregation operators:

  • AVE. Average
  • MAX. Maximum
  • MIN. Minimum
  • CNT. Count
  • SUM. Sum
field

Is the field whose value is to be retrieved.

reset_key

Identifies the point at which the retrieval break 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.

Note: The values used in the retrieval 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.

offset

Is the integer number of records to go forward (for a positive offset) or backward (for a negative offset) to retrieve the value.

If the offset is prior to the partition boundary sort value, the return will be the default value for the field. The calculation is performed prior to any WHERE TOTAL tests, but after WHERE_GROUPED tests.

Example: Retrieving a Previous Record With PARTITION_REF

The following request retrieves the previous record within the sort field PRODUCT_CATEGORY.

TABLE FILE WF_RETAIL_LITE
SUM DAYSDELAYED
COMPUTE NEWDAYS/I5=PARTITION_REF(DAYSDELAYED, PRODUCT_CATEGORY, -1);
BY PRODUCT_CATEGORY
BY PRODUCT_SUBCATEG
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. The first value within each sort break is zero because there is no prior record to retrieve.

The following request retrieves the average cost of goods from two records prior to the current record within the PRODUCT_CATEGORY sort field.

TABLE FILE WF_RETAIL_LITE
SUM COGS_US AVE.COGS_US AS Average
COMPUTE PartitionAve/D12.2M=PARTITION_REF(AVE.COGS_US, PRODUCT_CATEGORY, -2);
BY PRODUCT_CATEGORY
BY PRODUCT_SUBCATEG
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Replacing the function call with the following syntax changes the partition boundary to TABLE.

COMPUTE PartitionAve/D12.2M=PARTITION_REF(AVE.COGS_US, TABLE, -2);

The output is shown in the following image.

Reference: Usage Notes for PARTITION_REF

  • Fields referenced in the PARTITION_REF parameters but not previously mentioned in the request, will not be counted in column notation or propagated to HOLD files.