PCT_INCREASE: Calculating the Percentage Difference Between the Current and a Prior Value of a Field

How to:

Given an aggregated input field and a negative offset, PCT_INCREASE calculates the percentage difference between the value in the current row of the report output and one or more prior rows, within a sort break or the entire table. The reset point for the calculation is determined by the value of the PARTITION_ON parameter described in Specify the Partition Size for Simplified Statistical Functions.

The percentage increase is calculated using the following formula:

(current_value - prior_value) / prior_value

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.

Syntax: How to Calculate the Percentage Difference Between the Current and a Prior Value of a Field 

PCT_INCREASE([prefix.]field, offset)

where:

prefix

Is one of the following optional aggregation operators to apply to the field before using it in the calculation:

  • SUM. which calculates the sum of the field values. SUM is the default value.
  • CNT. which calculates a count of the field values.
  • AVE. which calculates the average of the field values.
  • MIN. which calculates the minimum of the field values.
  • MAX. which calculates the maximum of the field values.
  • FST. which retrieves the first value of the field.
  • LST. which retrieves the last value of the field.
field

Numeric

The field to be used in the calculation.

offset

Numeric

Is a negative number indicating the number of rows back from the current row to use for the calculation.

Example: PCT_INCREASE: Calculating the Percent Increase Between the Current and a Prior Value of a Field

The following request uses the default value of SET PARTITION_ON (PENULTIMATE) to calculate the percent increase within the PRODUCT_CATEGORY sort field between the current row and the previous row. 

SET PARTITION_ON=PENULTIMATE
TABLE FILE wflite
SUM QUANTITY_SOLD
COMPUTE PCTINC/D8.2p = PCT_INCREASE(QUANTITY_SOLD,-1);
BY PRODUCT_CATEGORY
BY PRODUCT_SUBCATEG
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. The first value for PCTINC is zero percent, as there is no prior value. The second value for PCTINC is the percent difference between the values for Headphones and Charger, the third is the percent difference between Universal Remote Controls and Headphones. Then, the calculations start over for Camcorder, which is the reset point.