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     [SOLVED]Computing totals
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Computing totals
 Login/Join
 
Member
posted
Hello everyone,
I think this an easy one...
I am trying to do a Recompute, but the results are not what I expect.
They are correct, but not what I wanted Smiler
I am just calculating the percentage of a count.
and then I want to compute the totals.
I thought with percentages I can just take an average, but thats obviously incorrect.
Here is my simple example using car file:
 
TABLE FILE car
SUM CNT.MODEL
COMPUTE percent_of/D12.2%=IF COUNTRY EQ 'ENGLAND' THEN 2.6 ELSE IF COUNTRY EQ 'FRANCE' THEN 10.4 ELSE IF COUNTRY EQ 'ITALY' THEN 12.07 ELSE 1.2 ;
COMPUTE Random_number/D12.2=CNT.MODEL * percent_of/100 ;
BY COUNTRY
ON TABLE HOLD AS File1 FORMAT BINARY
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET ASNAMES SUBST
ON TABLE SET HOLDATTRS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET STYLE *
END
-RUN

TABLE FILE File1
SUM MODEL
percent_of
Random_number
BY COUNTRY
ON TABLE PCHOLD FORMAT HTML
ON TABLE RECOMPUTE SUM. MODEL AVE. percent_of SUM. Random_number AS 'Total:'
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
END
-RUN


The result is:
 
COUNTRY	   MODEL	percent_of	Random_number
ENGLAND 	4	2.60%	        .10
FRANCE	        1	10.40%	        .10
ITALY	        4	12.07%	        .48
JAPAN	        2	1.20%	        .02
W GERMANY	7	1.20%	        .08
Total:	       18	5.49%	        .80


The Total for Model and Random_number is correct, its just the sum of the values, but the Total for percent_of field is an average(which of course is the correct average) but I need this total to be (total of random_number/total of Model * 100).
So the end result should be 4.44%

Thanks.

This message has been edited. Last edited by: nickz,
 
Posts: 12 | Registered: September 13, 2019Reply With QuoteReport This Post
Expert
posted Hide Post
Your recompute is meaningless because you do not have a compute in that particular table request.

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: 5684 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Since the definition (COMPUTE) of "percent_of" is a fix value depending on COUNTRY value and not a calculation of something,
I think that it may be done in several steps since I don't see how it can be in one step due to the "percent_of"

1- Perform the detailed data
2- Create the total row based on detail
3- Create final report with Detailed & Total data

Something such as this:
TABLE FILE CAR
SUM CNT.MODEL AS 'MODEL'
    COMPUTE percent_of/D12.2%=IF COUNTRY EQ 'ENGLAND' THEN 2.6 ELSE IF COUNTRY EQ 'FRANCE' THEN 10.4 ELSE IF COUNTRY EQ 'ITALY' THEN 12.07 ELSE 1.2 ;
    COMPUTE Random_number/D12.2=CNT.MODEL * percent_of/100 ;
BY TOTAL COMPUTE ROWID   /I1  = 1;
BY COUNTRY
ON TABLE HOLD AS DETDATA
END
-RUN

TABLE FILE DETDATA
SUM MODEL
    COMPUTE percent_of/D12.2% = Random_number / MODEL * 100;
    Random_number
BY TOTAL COMPUTE ROWID   /I1  = 2;
BY TOTAL COMPUTE COUNTRY /A10 = 'Total';
ON TABLE HOLD AS TOTDATA
END
-RUN

TABLE FILE DETDATA
SUM MODEL
    percent_of
    Random_number
BY ROWID   NOPRINT
BY COUNTRY
MORE
FILE TOTDATA
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Obviously your example is just that and (hopefully) bears the slimmest of resemblance to your actual code.

If you want your example to work as you want it, then change the second table request (the one with the actual recompute) to include a compute as you need -

TABLE FILE File1
SUM MODEL
COMPUTE percent_of/D7.2% = Random_number / MODEL * 100;
Random_number
BY COUNTRY


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: 5684 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Member
posted Hide Post
quote:
TABLE FILE File1
SUM MODEL
COMPUTE percent_of/D7.2% = Random_number / MODEL * 100;
Random_number
BY COUNTRY


Thank you!
 
Posts: 12 | Registered: September 13, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Computing totals

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