I would like to have a variance appear on a second level ACROSS hierarchy.
DEFINE FILE GGSALES YEAR/YY=DATE; END TABLE FILE GGSALES SUM GGSALES.SALES01.DOLLARS BY GGSALES.SALES01.CATEGORY ACROSS LOWEST GGSALES.SALES01.REGION COMPUTE VAR/D12.2=(C2-C1); ACROSS LOWEST GGSALES.SALES01.YEAR ON TABLE NOTOTAL END
It doesn't matter where I put the COMPUTE line I get the same results. The VAR comes at the end but I want the VAR to appear in each REGION.
ThanksThis message has been edited. Last edited by: Trudy,
Something like this maybe?
DEFINE FILE GGSALES YEAR/A4=EDIT(DATE,'9999'); END TABLE FILE GGSALES SUM GGSALES.SALES01.DOLLARS COMPUTE VAR/I08=IF LAST REGION EQ REGION THEN DOLLARS - LAST DOLLARS ELSE 0; BY GGSALES.SALES01.CATEGORY BY LOWEST GGSALES.SALES01.REGION BY LOWEST GGSALES.SALES01.YEAR ON TABLE HOLD AS SALES END TABLE FILE SALES SUM VAR AS DOLLARS COMPUTE YEAR/A4='VAR'; BY CATEGORY BY REGION WHERE YEAR EQ '1997' ON TABLE HOLD AS VAR END TABLE FILE SALES SUM DOLLARS BY CATEGORY ACROSS LOWEST REGION ACROSS LOWEST YEAR ON TABLE NOTOTAL MORE FILE VAR ENDThis message has been edited. Last edited by: Frans,
Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
There are probably several different ways to meet your requirements.
For these types of requirements I like to sometimes separate the data extraction/summarization/calculations from the report presentation to the user.
-DEFAULTH &LASTYEAR = 1996 -DEFAULTH &CURRENTYEAR = 1997 SET PAGE = OFF SET ACROSSLINE = SKIP SET ACROSSTITLE = SIDE -* -* Bucket Incoming Data. -* DEFINE FILE GGSALES YEAR/YY=DATE; YEAR1/D12.2 = IF YEAR EQ &LASTYEAR THEN DOLLARS ELSE 0; YEAR2/D12.2 = IF YEAR EQ &CURRENTYEAR THEN DOLLARS ELSE 0; END TABLE FILE GGSALES SUM YEAR1 YEAR2 COMPUTE VAR/D12.2 = YEAR1 - YEAR2; BY GGSALES.SALES01.CATEGORY BY GGSALES.SALES01.REGION ON TABLE HOLD END -RUN -* -* Report Presentation. -* TABLE FILE HOLD "Car Sales" "&LASTYEAR v. &CURRENTYEAR" SUM YEAR1 AS '&LASTYEAR' YEAR2 AS '&CURRENTYEAR' VAR AS 'Variance' BY CATEGORY ACROSS REGION AS 'Region:' ON TABLE SET STYLE * INCLUDE=jellybean_combo.sty, $ TYPE = TITLE, JUSTIFY = CENTER,$ TYPE = ACROSSTITLE, JUSTIFY = RIGHT,$ TYPE = ACROSSVALUE, JUSTIFY = CENTER,$ ENDSTYLE END
This message has been edited. Last edited by: David Briars,
Thanks everyone I used David's solution. I did try Frans solution but had an issue with the format of the field that was being totaled that I couldn't figure out.
|Powered by Social Strata|