Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     percentage on across totals
Go
New
Search
Notify
Tools
Reply
  
percentage on across totals
 Login/Join
 
Gold member
posted
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.
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Check this previous post:

http://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/1071068331

It has a very good discussion on this topic.


Ginny
---------------------------------
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Ginny,

Thank you for the link. The solutions provided do not work for me because I want the percentage on across total values.

Thanks.
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
RB,

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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
<JJI>
posted
RB,

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,
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     percentage on across totals

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.