As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
How can I get a percentage on across total for each across value?
Here is the code I have.
TABLE FILE CAR SUM DEALER_COST ACROSS COUNTRY ACROSS CAR ACROSS-TOTAL ON TABLE COLUMN-TOTAL WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' BY SEATS END -EXIT
Here is the output: ENGLAND ITALY CAR JAGUAR JENSEN TRIUMPH TOTAL ALFA ROMEO MASERATI TOTAL SEATS 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; Thanks.
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?
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007
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.