Syntax: How to Retrieve Prior Field Values for Use in a Calculation
PARTITION_REF([prefix.]field, {sortfield|TABLE}, -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 prior value is to be retrieved.
- {srtfield|TABLE}
-
Is the sort break within which to go back to retrieve the value. TABLE means retrieve the value without regard to sort breaks. Operations will not cross a partition boundary.
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.
- -offset
-
Is the integer number of records back to go 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 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 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.
