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,
Your recompute is meaningless because you do not have a compute in that particular table request.
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
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
|Powered by Social Strata|