How can I get a percentage on across total for each across value?
Here is the code I have.
TABLE FILE CAR
ACROSS CAR ACROSS-TOTAL
ON TABLE COLUMN-TOTAL
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'
Here is the output:
JAGUAR JENSEN TRIUMPH TOTAL ALFA ROMEO MASERATI TOTAL
2 7,427 . 4,292 11,719 11,320 25,000 36,320
4 . 14,940 . 14,940 4,915 . 4,915
5 11,194 . . 11,194 . . .
TOTAL 18,621 14,940 4,292 37,853 16,235 25,000 41,235
Now I want to get a percentage on each of these across totals.
So for example for the first row, I need to get the percentage (11,719/37,853)*100;
Check this previous post:
It has a very good discussion on this topic.
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
Thank you for the link. The solutions provided do not work for me because I want the percentage on across total values.
Searching my memory did not reveal any standard thing that we can use to do this. (Web)Focus does not have any setting or option or such to do this by using one or more simple keywords.
Having said that, the following code will do what you want, although it is rather specific to the car database. You may however be able to transpose it to some other datasource.
Anyway, here goes:
SET ASNAMES=ON, HOLDLIST=PRINTONLY TABLE FILE car BY COUNTRY BY CAR WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' ON TABLE HOLD FORMAT ALPHA END -RUN -SET &C = 0; -:LOOP -READ HOLD &COU.10 &CAR.16 -IF &IORETURN NE 0 THEN GOTO :ENDLOOP; -SET &C = &C+1; -SET &COU.&C = &COU; -SET &CAR.&C = &CAR; -TYPE &COU.&C &CAR.&C -GOTO :LOOP -:ENDLOOP TABLE FILE car SUM DEALER_COST BY SEATS ACROSS COUNTRY ACROSS CAR ACROSS-TOTAL WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' ON TABLE HOLD FORMAT ALPHA END -RUN TABLE FILE HOLD SUM E05 NOPRINT E08 NOPRINT PRINT E02 AS '&COU1 , &CAR1' E03 AS '&COU2 , &CAR2' E04 AS '&COU3 , &CAR3' E05 AS 'TOTAL, &COU3' COMPUTE PER/D6.2 = (C6/C1)*100; E06 AS '&COU4 , &CAR4' E07 AS '&COU5 , &CAR5' E08 AS 'TOTAL, &COU4' COMPUTE PER/D6.2 = (C10/C2)*100; BY SEATS ON TABLE COLUMN-TOTAL END
Let us know if this has been useful?
This is indeed a difficult one. I'd like Gamp's solution but as he said is rather specific to the car database.
My solution is not exactly what you want, but it's a start and it's not tied to a specific DB. This means that you don't have to know how many across values you have.
SET ASNAMES = ON TABLE FILE CAR SUM DEALER_COST PCT.DEALER_COST/D6.2% WITHIN COUNTRY AS 'PERCENT' BY COUNTRY BY CAR BY SEATS ON TABLE COLUMN-TOTAL WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' ON TABLE HOLD AS TEST END TABLE FILE TEST SUM DEALER_COST PERCENT ACROSS COUNTRY ACROSS CAR ACROSS-TOTAL BY SEAT ON TABLE COLUMN-TOTAL END
The only difference with Gamp's solution is that I have a percentage for every across value and not only on the totals. I'll see if I can solve this, when I've got a bit more time.
Hope this helps,
|Powered by Social Strata|