Performing Calculations at Sort Field Breaks

How to:

Reference:

You can use the RECAP and COMPUTE commands to create subtotal values in a calculation. The subtotal values are not displayed. Only the result of the calculation is shown on the report.


Top of page

x
Syntax: How to Use Subtotals in Calculations

Both the RECAP and COMPUTE commands have similar syntax to other total and subtotal commands.

{BY|ON} fieldname1 {RECAP|COMPUTE} fieldname2[/format] = expression;
                                      [WHEN expression;]

where:

fieldname1

Is the field in the BY phrase. Each time the BY field changes value, a new recap value is calculated.

fieldname2

Is the field name that contains the result of the expression.

/format

Can be any valid format. The default is D12.2.

expression

Can be any valid expression, as described in Using Expressions. You must end the expression with a semicolon.

WHEN expression

Is for use with RECAP only. It specifies the conditional display of RECAP lines as determined by a Boolean expression (see Conditionally Displaying Summary Lines and Text). You must end the expression with a semicolon.


Top of page

x
Reference: Usage Notes for RECAP and COMPUTE


Example: Using RECAP

The following request illustrates the use of RECAP (DEPT_NET) to determine net earnings for each department:

TABLE FILE EMPLOYEE
SUM DED_AMT AND GROSS
BY DEPARTMENT BY PAY_DATE
ON DEPARTMENT RECAP DEPT_NET/D8.2M = GROSS-DED_AMT;
WHEN PAY_DATE GT 820101
END

The output is:

DEPARTMENT  PAY_DATE          DED_AMT            GROSS
----------  --------          -------            -----
MIS         81/11/30        $1,406.79        $2,147.75
            81/12/31        $1,406.79        $2,147.75
            82/01/29        $1,740.89        $3,247.75
            82/02/26        $1,740.89        $3,247.75
            82/03/31        $1,740.89        $3,247.75
            82/04/30        $3,386.73        $5,890.84
            82/05/28        $3,954.35        $6,649.50
            82/06/30        $4,117.03        $7,460.00
            82/07/30        $4,117.03        $7,460.00
            82/08/31        $4,575.72        $9,000.00
                                                      
** DEPT_NET           $22,311.98                      
                                                      
PRODUCTION  81/11/30          $141.66          $833.33
            81/12/31          $141.66          $833.33
            82/01/29        $1,560.09        $3,705.84
            82/02/26        $2,061.69        $4,959.84
            82/03/31        $2,061.69        $4,959.84
            82/04/30        $2,061.69        $4,959.84
            82/05/28        $3,483.88        $7,048.84
            82/06/30        $3,483.88        $7,048.84
            82/07/30        $3,483.88        $7,048.84
            82/08/31        $4,911.12        $9,523.84
                                                      
** DEPT_NET           $27,531.14


Example: Using Multiple RECAP Commands

You can include multiple RECAP or COMPUTE commands in a request. This option enables you to perform different calculations at different control breaks.

The following request illustrates the use of multiple RECAP commands.

TABLE FILE SALES
SUM UNIT_SOLD AND RETURNS
WHERE AREA EQ 'U' 
BY DATE BY AREA BY PROD_CODE
ON DATE RECAP
DATE_RATIO=RETURNS/UNIT_SOLD;
ON AREA UNDER-LINE RECAP
AREA_RATIO=RETURNS/UNIT_SOLD;
END

The output is:

DATE   AREA  PROD_CODE  UNIT_SOLD  RETURNS
----   ----  ---------  ---------  -------
10/17  U     B10               30        2
             B17               20        2
             B20               15        0
             C17               12        0
             D12               20        3
             E1                30        4
             E3                35        4
                                          
** AREA_RATIO                      .09    
                                          
** DATE_RATIO                      .09    
                                          
------------------------------------------
10/18  U     B10               13        1
                                          
** AREA_RATIO                      .08    
                                          
** DATE_RATIO                      .08    
                                          
------------------------------------------
10/19  U     B12               29        1
                                          
** AREA_RATIO                      .03    
                                          
** DATE_RATIO                      .03    
                                          
------------------------------------------

WebFOCUS