 Focal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Computing totals
 Go New Search Notify Tools Reply  [SOLVED]Computing totals Member posted March 24, 2020 10:30 AM
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 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, 2019   IP
Expert posted March 24, 2020 11:13 AM Hide Post
Your recompute is meaningless because you do not have a compute in that particular table request.

T

 In FOCUSsince 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: 5661 | Location: United Kingdom | Registered: April 08, 2004   IP
Virtuoso posted March 24, 2020 11:24 AM 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: 2296 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013   IP
Expert posted March 24, 2020 11:25 AM 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 FOCUSsince 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: 5661 | Location: United Kingdom | Registered: April 08, 2004   IP
Member posted March 24, 2020 11:39 AM 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, 2019   IP
 Reply  Focal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Computing totals