howdy there, im strugglin to get WF to summarize correctly, on my table file im calculating the proportion of the total sales, on the field
COM_MIX_RTL is working fine on all the iterations but it looks like im doing something wrong on the table summarize as is giving a weird result, I tried printing the TOT.RTL_COM_TY as new computer var and it looks that it will sum it for each iteration on the summarize statement, is there a way to hold the same value for TOT.RTL_COM_TY even on the summarize statement?

  
TABLE FILE FF_CATDECK_SALES_SS
SUM
RTL_COM_TY/D12
COMPUTE RTL_CHG_COM/D12.3= IF RTL_COM_TY = 0 THEN 0 ELSE IF RTL_COM_LY = 0 THEN 0 ELSE (RTL_COM_TY-RTL_COM_LY)/RTL_COM_LY;
QTY_COM_TY
COMPUTE QTY_CHG_COM/D12.3= IF QTY_COM_TY = 0 THEN 0 ELSE IF QTY_COM_LY = 0 THEN 0 ELSE (QTY_COM_TY-QTY_COM_LY)/QTY_COM_LY;
COMPUTE COM_AVG_RTL_TY/D12.2= RTL_COM_TY/QTY_COM_TY;
COMPUTE COM_AVG_RTL_LY/D12.2= RTL_COM_LY/QTY_COM_LY;
COMPUTE COM_GM_TY/D12.4= (RTL_COM_TY-COST_COM_TY)/RTL_COM_TY;
COMPUTE COM_GM_LY/D12.4= (RTL_COM_LY-COST_COM_LY)/RTL_COM_LY; NOPRINT
COMPUTE COM_GM_CHG/D12B =  (COM_GM_TY - COM_GM_LY ) *10000; 
COMPUTE COM_MIX_RTL/D12= RTL_COM_TY/TOT.RTL_COM_TY*100;
WHERE SUB_CATEGORY IN ('cat1','cat2','cat4','cat5')
BY SUB_CATEGORY
ON TABLE SUMMARIZE
END

Raphael,

What SUMMARIZE does is sums all fields in the COMPUTE expressions and then reuse the expressions with the totaled fields. Hence the problem you experienced.
The only elements that are not summed are constants. So, I suggest the following, using the CAR file as an example:

  
TABLE FILE CAR
SUM TOT.SALES
ON TABLE HOLD AS TS FORMAT ALPHA
END
-RUN
-READFILE TS
TABLE FILE CAR
SUM SALES/D10
COMPUTE TSALES/D10=SALES / &SALES * 100;
BY COUNTRY
ON TABLE SUMMARIZE  
END

Hello Danny, I was using that work around before but was expecting this could be done in one step, I guess is not possible, thanks anyway!

Single step with multi-verb:

TABLE FILE CAR
 SUM SALES/D10 NOPRINT
 SUM SALES/D10
 COMPUTE TSALES/D10 = SALES / C1 * 100;
 BY COUNTRY
 ON TABLE SUMMARIZE
END

Or....

DEFINE FILE CAR
 TOT_SALES/D10 = SALES ;
END
-*
TABLE FILE CAR
 SUM TOT_SALES  NOPRINT
 SUM SALES/D10
 COMPUTE TSALES/D10 = SALES / TOT_SALES * 100;
 BY COUNTRY
 ON TABLE SUMMARIZE
END

Or....

TABLE FILE CAR
 SUM COMPUTE TOT_SALES/D10 = SALES; NOPRINT
 SUM SALES/D10
 COMPUTE TSALES/D10 = SALES / TOT_SALES * 100;
 BY COUNTRY
 ON TABLE SUMMARIZE
END

This message has been edited. Last edited by: Dan Satchell,

Dan thank you this kinda does what I want except that this will cause the 2 line on total issue