Please consider the code below. I am calculating the total revenue as the product of retail_price and units_sold for a particular store code "K1". I defined a margin percentage of 10% for item code "B10" and 20% for all other items. Total Revenue for this store should be 56.08, and total margin should be 9.93.

 
DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
END
 
TABLE FILE SALES
 
SUM 
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
COMPUTE REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ; AS 'Total Revenue'
COMPUTE	TOTAL_MARGIN = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT; AS 'Total Margin'
BY STORE_CODE AS 'Store ID'
BY PROD_CODE AS 'Item ID'
BY RETAIL_PRICE AS 'Retail Price'
BY MARGIN_PCT AS 'Magin %'
WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Now if you comment out the last 3 BY Fields for Prod_Code, Retail Price, and Margin_PCT and execute , you will notice that the total revenue and total margin calculation are not correct.

 
DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
END
 
TABLE FILE SALES
 
SUM 
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
COMPUTE REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ; AS 'Total Revenue'
COMPUTE	TOTAL_MARGIN = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT; AS 'Total Margin'
BY STORE_CODE AS 'Store ID'
-*BY PROD_CODE AS 'Item ID'
-*BY RETAIL_PRICE AS 'Retail Price'
-*BY MARGIN_PCT AS 'Magin %'
WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 

I understand that webfocus is now taking (1.99 + .49) * 42 which equals 104.16 for total revenue ,
and taking (104.16 * .30) which equals for 31.25 . However, these amounts are obviously incorrect based on the data we have.

How could one solve this problem so that the total revenue & margin for this store is calculated correctly WITHOUT using a HOLD table?
Your help is greatly appreciated

This message has been edited. Last edited by: Joey Sandoval,

You're using COMPUTE within the table body.
These computed fields will only be calculated AFTER completing the internal matrix, ie after having summed and sorted. Thus COMPUTE will use the aggregated values.
The obvious solution to this is to do your math in DEFINE, which will calculate with the individual records and will produce the correct result:

DEFINE FILE SALES
MARGIN_PCT/D6.2    = IF PROD_CODE = 'B10' THEN 0.1 ELSE 0.2;
NUMBER_OF_ITEMS/I5 = 1;
NUMBER_OF_PURCH/I5 = UNIT_SOLD;
REVENUE/D12.2      = UNIT_SOLD * RETAIL_PRICE ;
TOTAL_MARGIN/D12.2 = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT;
END

TABLE FILE SALES
SUM   
      NUMBER_OF_ITEMS    AS '# of Items'
      NUMBER_OF_PURCH    AS 'Total Units Sold '
      REVENUE            AS 'Total Revenue'
      TOTAL_MARGIN       AS 'Total Margin'

BY STORE_CODE AS 'Store ID'
-*BY PROD_CODE AS 'Item ID'
-*BY RETAIL_PRICE AS 'Retail Price'
-*BY MARGIN_PCT AS 'Margin %'

WHERE STORE_CODE EQ 'K1';

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Try this,

DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ;
TOTAL_MARGIN/D12.2 = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT;
END

TABLE FILE SALES

SUM
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
REVENUE AS 'Total Revenue'
TOTAL_MARGIN AS 'Total Margin'
BY STORE_CODE AS 'Store ID'

WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Thank you for the clarification. I was misled into thinking that this could be accomplished using computes within a sum field.

Revenue and TOTAL_MARGIN need to be defined fields. Remember computes are evaluated after all data has been aggregated, hence your margin here would be .1* the number of B10 items plus .2*the number of non B10 items. Move REVENUE and TOTAL_MARGIN to your define and life will be good.