Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Percentage against a total

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Percentage against a total
 Login/Join
 
Member
posted
I have searched and found some examples of getting a percentage against subtotals - but I have either not understood what the document was saying, or it has not worked.

I am trying to get a percentage of each person's mailbox size compared to their departments. I am using SQL pass-through to provide the fields. If I use the Compute with LAST Department - the first employee in the department always say 100% - so I am not getting the percentage on the Total of the Department.

Any help in pointing me in the right direction would be greatly appreciated

This message has been edited. Last edited by: <Kathryn Henning>,


8.1.5m
Windows Server 2012
SQL 2012
 
Posts: 3 | Location: Wyoming/Montana | Registered: May 09, 2013Report This Post
Master
posted Hide Post
Hi,

Try below code.

APP PATH IBISAMP
-RUN
TABLE FILE CAR
SUM
DEALER_COST AS 'D_C_COUNTRY'
BY COUNTRY NOPRINT
SUM
DEALER_COST AS 'D_C_CAR'
BY COUNTRY
BY CAR
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END
-RUN
TABLE FILE HOLD
PRINT
D_C_CAR AS 'DEALER_COST'
COMPUTE PERCENTAGE_1/D20.2%=D_C_CAR*100/D_C_COUNTRY; AS 'PERCENTAGE'
BY D_C_COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT HTML
END


Thanks,
Ram

This message has been edited. Last edited by: Ram Prasad E,
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Virtuoso
posted Hide Post
I reckon that this is the best use of WITHIN.
TABLE FILE GGSALES
SUM       DOLLARS
      PCT.DOLLARS WITHIN REGION
BY REGION
BY STCD
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
Hi KL and welcome to the Point.

I think that Ram missed the point of your request and missing out the fact that you are having issues with the Percentage using LAST, but we've all done that before! Smiler

Try this code, it hits the sample table EMDPATA so that the example is similar to what you're after - data context wise. I use SALARY instead of inbox size but the method is the same for what you require.

Hopefully your installation still has the sample files installed with the server. I know some Companies omit these but they are so useful when asking for help (giving examples and repros for faults) I cannot understand why they wouldn't want to install them? Perhaps I'm too long in the tooth and have seen how useful they are? ..... Anyway, good luck

-* WebFOCUS Version used: 8.0.06
DEFINE FILE EMPDATA
  FULLNAME/A100 = LASTNAME || (', ' | FIRSTNAME);
END
TABLE FILE EMPDATA
-* Get the total at the right sort level that you wish to use as the divisor
-* but NOPRINT it as we don't want to see it in the report
  SUM SALARY NOPRINT
   BY DEPT

-* Now we can reference that report field using column notation,
-* this is the first non sort field so the notation is C1
  SUM SALARY
      COMPUTE PERCENTAGE/D6.2% = SALARY / C1 * 100;
   BY DEPT
   BY FULLNAME

-* Let us show the total being used in the percentage calculation in a subtotal for the sort value
ON DEPT SUBTOTAL AS '*'

-* As we're using a computed percentage, we need to use RECOMPUTE on the grandtotal
-* Try it with and without to see the difference that it makes ....
ON TABLE RECOMPUTE

-* This line turns of paging essentially
ON TABLE SET LINES 999999

-* This line turns off the automatic page numbering and some of the leading blank line(s)
ON TABLE SET PAGE NOLEAD

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  SIZE=9, FONT=ARIAL, SQUEEZE=ON, GRID=OFF, $
ENDSTYLE
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
quote:
best use of WITHIN

Good call Alan, why do we (i.e. me!) forget these recent syntax inclusions?

However, what about the GRANDTOTAL? I've not been able to get the percentage correct but then it is the end of the week! No doubt you've got that licked as well! Wink I know this is due to the WITHIN DEPT.

DEFINE FILE EMPDATA
  FULLNAME/A100 = LASTNAME || (', ' | FIRSTNAME);
END

TABLE FILE EMPDATA
  SUM SALARY
      PCT.SALARY/D6.2% WITHIN DEPT
   BY DEPT
   BY FULLNAME
ON DEPT SUBTOTAL AS '*'

-* None of these produce the correct percentage ....
-*ON TABLE SUBTOTAL
-*ON TABLE SUMMARIZE
-*ON TABLE RECOMPUTE
ON TABLE COLUMN-TOTAL

ON TABLE SET LINES 999999
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  SIZE=9, FONT=ARIAL, SQUEEZE=ON, GRID=OFF, $
ENDSTYLE
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
Thanks for pointing it Anthony. I missed that requirment.

-Ram
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Member
posted Hide Post
Tony,

Your procedure works beautifully - thank you so much for explaining each step - it really helped in converting it to my file - especially explaining what the column notations are.

Thanks again.


8.1.5m
Windows Server 2012
SQL 2012
 
Posts: 3 | Location: Wyoming/Montana | Registered: May 09, 2013Report This Post
Expert
posted Hide Post
KL

As the old saying goes -

"give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime"

Glad it helped you out Smiler

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
quote:
I missed that requirment

Hi Ram,

If I had a pound for each time I've started to reply and then decided to double check I understood what was being asked ........

We've all done and will continue to do it! Confused

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Percentage against a total

Copyright © 1996-2020 Information Builders