Using EXL2K Formula With Prefix Operators

EXL2K FORMULA output supports prefix operators that are used on summary lines generated by WebFOCUS commands, such as SUBTOTAL and RECOMPUTE. Where a corresponding formula exists in Excel, these prefix operators are translated into the equivalent Excel summarization formula. The results of prefix operators used directly against retrieved data continue to be passed to Excel as values, not formulas.

The following table identifies the prefix operators supported by EXL2K FORMULA when used on summary lines, and the Excel formula equivalent placed in the generated worksheet.

Prefix Operator	Excel Formula Equivalent
SUM.	=SUM()
AVE.	=AVERAGE()
CNT.	=COUNT()
MIN.	=MIN()
MAX.	=MAX()

The following prefix operators are not translated to formulas when used on summary lines in EXL2K FORMULA.

ASQ.
FST.
LST.

Note:

When using a prefix operator on a field specified directly against retrieved data, there is no space between the prefix operator and the field on which it operates.
For example, in the following aggregating display command, the AVE. prefix operator operates on the DEALER_COST field:
```
SUM AVE.DEALER_COST
```
When using a prefix operator on a summary line, you must leave a space between the prefix operator and the aggregated field on which it operates.
In the following summary command, the MAX. prefix operator operates on the DOLLARS field at the REGION sort break. Note the required blank space between the prefix operator and the field name:
```
ON REGION RECOMPUTE MAX. DOLLARS 
```

Top of page

Example: Using a Summary Prefix Operator With Format EXL2K FORMULA

In the following request against the GGSALES data source, the RECOMPUTE command for the REGION sort field calculates the maximum of the aggregated DOLLARS field and the minimum of the aggregated BUDDOLLARS field:

TABLE FILE GGSALES
SUM UNITS DOLLARS BUDDOLLARS
AND COMPUTE DIFF/I10= DOLLARS-BUDDOLLARS;
BY REGION
BY CATEGORY
WHERE CATEGORY EQ 'Food' OR 'Coffee'
WHERE REGION EQ 'West' OR 'Midwest'
ON REGION RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS DIFF
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

On the output, the cell that represents the recomputed DOLLARS for the Midwest region has been generated as the formula =MIN(E2:E3).

Top of page

Example: Using a Prefix Operator on a Display Command With FORMAT EXL2K FORMULA

In the following request against the GGSALES data source, the CNT., AVE., and PCT. prefix operators are used in the SUM display command:

TABLE FILE GGSALES
SUM UNITS
CNT.UNITS
AVE.UNITS
PCT.UNITS
BY REGION 
BY ST 
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

The output shows that the prefix operators were not passed to Excel as formulas, they were passed as data values.

WebFOCUS