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 name of a sort field in the request.
- PRESET, which uses the value of the PARTITION_ON parameter, as described in Specify the Partition Size for Simplified Statistical Functions.
- TABLE, which indicates that there is no break on a sort field.
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
ENDThe 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
ENDThe 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.
