Describing a Calculated Value: COMPUTE

COMPUTE commands can be included in Master Files and referenced in subsequent TABLE requests, enabling you to build expressions once and use them in multiple requests.

Syntax: How to Include a COMPUTE Command in a Master File

COMPUTE fieldname/fmt=expression;
  [,TITLE='title',] 
  [TITLE_ln='titleln', ... ,]
  [,DESC[CRIPTION]='desc',] 
  [DESC_ln='descln', ... ,]$

where:

fieldname: Is name of the calculated field.
fmt: Is the format and length of the calculated field.
expression: Is the formula for calculating the value of the field.
TITLE='title': Is a column title for the calculated field in the default language.
TITLE_ln='titleln': Is a column title for the calculated field in the language specified by the language code ln.
DESC[CRIPTION]='desc': Is a description for the calculated field in the default language.
DESC_ln='descln': Is a description for the calculated field in the language specified by the language code ln.

Reference: Usage Notes for COMPUTE in a Master File

In all instances, COMPUTEs in the Master File have the same functionality and limitations as temporary COMPUTEs. Specifically, fields computed in the Master File must follow these rules:

They cannot be used in JOIN, DEFINE, or ACROSS phrases, or with prefix operators.
When used as selection criteria, syntax is either IF TOTAL field or WHERE TOTAL field.
When used as sort fields, syntax is BY TOTAL COMPUTE field.
To insert a calculated value into a heading or footing, you must reference it prior to the HEADING or FOOTING command.

Note: WebFOCUS Maintain does not currently support using COMPUTEs in Master Files, and these COMPUTEs do not appear in the Update Assist Wizard.

Top of page

Example: Coding a COMPUTE in the Master File and Accessing the Computed Value

Use standard COMPUTE syntax to add a calculated value to your Master File. You can then access the calculated value by referencing the computed fieldname in subsequent TABLE requests. When used as a verb object, as in the following example, the syntax is SUM (or PRINT) COMPUTE field.

The following is the SALESTES Master File (the SALES FILE modified with an embedded COMPUTE):

FILENAME=SALESTES, SUFFIX=FOC,
SEGNAME=STOR_SEG, SEGTYPE=S1,
   FIELDNAME=STORE_CODE,  ALIAS=SNO,  FORMAT=A3,   $
   FIELDNAME=CITY,        ALIAS=CTY,  FORMAT=A15,  $
   FIELDNAME=AREA,        ALIAS=LOC,  FORMAT=A1,   $
 
SEGNAME=DATE_SEG, PARENT=STOR_SEG, SEGTYPE=SH1,
   FIELDNAME=DATE,        ALIAS=DTE,  FORMAT=A4MD, $
 
SEGNAME=PRODUCT, PARENT=DATE_SEG, SEGTYPE=S1,
   FIELDNAME=PROD_CODE,     ALIAS=PCODE,   FORMAT=A3,    FIELDTYPE=I, $
   FIELDNAME=UNIT_SOLD,     ALIAS=SOLD,    FORMAT=I5,    $
   FIELDNAME=RETAIL_PRICE,  ALIAS=RP,      FORMAT=D5.2M, $
   FIELDNAME=DELIVER_AMT,   ALIAS=SHIP,    FORMAT=I5,    $
   FIELDNAME=OPENING_AMT,   ALIAS=INV,     FORMAT=I5,    $
   FIELDNAME=RETURNS,       ALIAS=RTN,     FORMAT=I3,    MISSING=ON, $
   FIELDNAME=DAMAGED,       ALIAS=BAD,     FORMAT=I3,    MISSING=ON, $
 
   COMPUTE REVENUE/D12.2M=UNIT_SOLD*RETAIL_PRICE;

The following TABLE request uses the REVENUE field:

TABLE FILE SALESTES
HEADING CENTER
"NEW YORK PROFIT REPORT"
" "
SUM UNIT_SOLD AS 'UNITS,SOLD' RETAIL_PRICE AS 'RETAIL_PRICE'
COMPUTE REVENUE;
BY PROD_CODE AS 'PROD,CODE'
WHERE CITY EQ 'NEW YORK'
END

The output is:

           NEW YORK PROFIT REPORT
 
  PROD  UNITS
  CODE  SOLD   RETAIL_PRICE          REVENUE
  ----  ----   ------------          -------
  B10      30          $.85           $25.50
  B17      20         $1.89           $37.80
  B20      15         $1.99           $29.85
  C17      12         $2.09           $25.08
  D12      20         $2.09           $41.80
  E1       30          $.89           $26.70
  E3       35         $1.09           $38.15