Can some one explain me how to handle Null values when we are calculating Running Totals. For Example i have a Across Report.
SET MISSING =ON
-*SET NODATA = 0
TABLE FILE CAR
SUM
SALES
COMPUTE RUNNING_SALES/I6= RUNNING_SALES+ SALES;
BY CAR
WHERE CAR IN ( 'JAGUAR' , 'JENSEN' , 'ALFA ROMEO')
ACROSS COUNTRY
END
-EXIT
COUNTRY
ENGLAND ITALY
CAR SALES RUNNING_SALES SALES RUNNING_SALES
ALFA ROMEO . . 30200 30200
JAGUAR 12000 12000 . .
JENSEN 0 12000 . .
i want to display the report in such a way that missing shuld be 0 and running totals shuld be calculated.
COUNTRY
ENGLAND ITALY
CAR SALES RUNNING_SALES SALES RUNNING_SALES
ALFA ROMEO 0 0 30200 30200
JAGUAR 12000 12000 0 30200
JENSEN 0 12000 0 30200
Please share any thoughts.
ThanksThis message has been edited. Last edited by: Kerry,
WebFocus 7.1.4 xp html,excel,pdf
November 22, 2011, 08:31 AM
dhagen
Calc the running totals first, then refer to them in a compute after the across:
SET NODATA = 0
TABLE FILE CAR
SUM COMPUTE C_SALES/I6=SALES + LAST C_SALES; NOPRINT
BY CAR
SUM SALES
BY CAR
WHERE CAR IN ( 'JAGUAR' , 'JENSEN' , 'ALFA ROMEO')
ACROSS COUNTRY
COMPUTE RUNNING_SALES/I6= C2;
END
C2 is the second verb object in the internal matrix (C_SALES)
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
November 22, 2011, 08:40 AM
<FreSte>
souji,
In your example one can (mis)read the report. It looks like JAGUAR and JENSEN has RUNNING_SALES of 30200 in ITALY.
Maybe you can usethis instead (just a thought)
-Fred
SET NODATA = 0
TABLE FILE CAR
SUM
SALES/I9C
COMPUTE RUNTOT/I9C =
IF COUNTRY EQ LAST COUNTRY
THEN LAST RUNTOT + SALES
ELSE SALES;
BY COUNTRY
BY CAR
ON TABLE HOLD AS HLD1
END
TABLE FILE HLD1
SUM
SALES
RUNTOT
BY CAR
ACROSS COUNTRY
ON TABLE SET PAGE NOPAGE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT ,UNITS=PTS ,FONT='VERDANA', SIZE=8 ,$
TYPE=REPORT ,BORDER=1 ,BORDER-COLOR=RGB(210 210 210) ,$
TYPE=TITLE ,STYLE=BOLD ,BACKCOLOR=RGB(230 230 230) ,$
TYPE=ACROSSTITLE,STYLE=BOLD ,BACKCOLOR=RGB(230 230 230) ,$
TYPE=ACROSSTITLE,JUSTIFY=CENTER ,$
TYPE=ACROSS ,JUSTIFY=CENTER ,$
TYPE=SUBTOTAL ,STYLE=BOLD ,BACKCOLOR=RGB(210 210 210) ,$
TYPE=DATA ,TOPGAP=2 ,BOTTOMGAP=2 ,$
TYPE=DATA ,BACKCOLOR=(RGB(255 255 255) RGB(245 245 245)),$
ENDSTYLE
END