Reporting Language > Simplified Analytic Functions > PARTITION_REF: Using Prior Field Values in Calculations

PARTITION_REF: Using Prior 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. The PARTITION_REF function enables you to specify both how many rows back to go in order to retrieve a prior value, and a sort break within which the prior value calculation will be contained.

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.

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.

WebFOCUS

Feedback