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,

Your recompute is meaningless because you do not have a compute in that particular table request.

T

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

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

quote:

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

Thank you!