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     Percent Subtotal in Info Assist
Go
New
Search
Notify
Tools
Reply
  
Percent Subtotal in Info Assist
 Login/Join
 
Member
posted
Hi All – I’m looking for some help with producing a percent of subtotal calculation in InfoAssist (v7.7.03). I know that there are over 40 posts in this forum for Dev Studio, however I am unable to reproduce the same workaround in InfoAssist. I’m basically looking for something like this:

Region Division Count Percent
A D1 1 33%
A D2 2 66%

TOTAL A 3 100%
B D3 2 40%
B D4 3 60%

TOTAL B 5 100%

Any help would be appreciated.
 
Posts: 5 | Registered: December 13, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
You could use the PCT prefix and WITHIN syntax. I don't use InfoAssit I am not sure if the GUI gives you these options.

TABLE FILE CAR
SUM
   DEALER_COST
   PCT.DEALER_COST/D20.2% WITHIN COUNTRY AS 'PERCENT'
    
BY COUNTRY
BY MODEL
ON COUNTRY SUBTOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END  


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
 
Posts: 320 | Location: Memphis, TN | Registered: February 12, 2008Reply With QuoteReport This Post
Member
posted Hide Post
Hey Max - Thank you for your response. The WITHIN command doesn't seem to work in InfoAssist. I get the following error when I try to use in a compute command:

(FOC224) SYNTAX ERROR: WITHIN BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Here's my formula:

PCT.Total WITHIN Division

Formatted as D12.2%


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 5 | Registered: December 13, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
How is your count field being calculated?

I have just created an IA report using GGSALES, added a define - TOT_COUNT/D12 = 1, and then computed a field as PCENT/D12.2% = PCT.TOT_COUNT. This seemed to work OK for me.

This should equate to -

DEFINE FILE GGSALES
  TOT_COUNT/D12 = 1;
END
TABLE FILE GGSALES
  SUM TOT_COUNT
      COMPUTE PCENT/D12.2% = PCT.TOT_COUNT;
   BY REGION
   BY ST
ON TABLE RECOMPUTE
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Hey Tony - maybe I'm missing something, but that doesn't give me a percent of Subtotal. The percent calculation still evaluates to percent of Column total.

My data does not contain aggregation measures, so I often have to count a text field to produce summaries. e.g. CNT.CustomerNumber where CustomerNumber is alphanumeric.

To be a little bit more clear, here's what my output looks like now:

Category Result Count Percent
Division_1 Failed 47 .91
Success 115 2.23
*TOTAL Division_1 162 3.14
---------------------------------------
Division_2 Failed 927 17.97
Success 4069 78.89
*TOTAL Division_2 4996 96.86
----------------------------------------
TOTAL 5158 100.00

This message has been edited. Last edited by: IA_Newbie,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 5 | Registered: December 13, 2012Reply With QuoteReport This Post
Member
posted Hide Post
Oops! That doesn't look clear at all! Smiler

Sorry, I wish I could upload an image to show you what I'm seeing.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 5 | Registered: December 13, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
If you put example output then place it in a code tag pairing (the </> in the reply dialog).

If you have this output then I guess what you want is the percentages to be calculated within Category? As Max suggested, the WITHIN would be the best method, but I don't know whether you can achieve that in IA. If I get a chance tomorrow, I'll see what I can achieve.

Category          Result  Count  Percent  Wanted
Division_1        Failed     47      .91   29.01
                  Success   115     2.23   70.99
*TOTAL Division_1           162     3.14  100.00
------------------------------------------------
Division_2        Failed    927    17.97   18.55
                  Success  4069    78.89   81.45
*TOTAL Division_2          4996    96.86  100.00
------------------------------------------------
TOTAL                      5158   100.00  100.00

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hello,
I'm not sure if you have gotten your answer for this issue. But the IA product, beging purely GUI. After you have gragged your fields to the "Query" section of the tool..r-click on the field you want to "Sub-tot" on, select "Break" in the context menu and select "on:...simulary the fields that you want to be represented in a "%" format, r-click and choose "Edit Format" and select the "%' box....and that should give what you want.

Hope this helps




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the suggestion tlbrydie2. My question was more around the percent of subtotal calculation itself. If you look at Tony's last post, I'm looking for a way to calculate the "Wanted" column vs. "Percent", which the tool does by default.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 5 | Registered: December 13, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Percent Subtotal in Info Assist

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