Hi,

I am trying to show subtotals by two different BY Fields (year and unit) but the results are adding the rates.
So I have one column with the numerator, one column with the denominator, then the third column is column1/column2 for the rate (it is not a compute it is calculated in the metadata).

When I try to subtotal by the year and the unit, it sums the first two columns (correct) but also sums the third column (the rate) when it should be re-calculating it. I tried to use the summarize command but that does not work.

My code is basically:

PRINT
Numerator
Denominator
Rate
BY LOWEST Fiscal_Year
BY LOWEST UNIT
BY LOWEST MONTH_OF_YEAR_NAME AS '' ROWS 'Apr' OVER 'May' OVER 'Jun' OVER 'Jul' OVER 'Aug' OVER 'Sept' OVER 'Oct' OVER 'Nov' OVER 'Dec' OVER 'Jan' OVER 'Feb' OVER 'Mar'

ON Fiscal_Year SUMMARIZE
ON UNIT SUMMARIZE

WHERE ( UNIT EQ &UNIT.(OR(FIND FACT_SELECTED_CMG.UNIT IN FACT_SELECTED_CMG)).UNIT:. );

This message has been edited. Last edited by: hfung1,

As far as I know, you will have to perform a COMPUTE with the first two column and avoid using the third one (calculated rate from metadata).

Having the "COMPUTEd" field already in the metadata it's a good thing while you don't need to summarize (it is faster), otherwise you have to perform the calculation on execution using a COMPUTE.

look for RECOMPUTE instead of SUMMARIZE

Still, RECOMPUTE needs a COMPUTEd field to work.

Something such as:

PRINT 
 Numerator 
 Denominator
-* For Percentage Rate, otherwise do not multiply by 100
 COMPUTE Rate /P8.2C = Numerator / Denominator * 100;
BY LOWEST Fiscal_Year
BY LOWEST UNIT
BY LOWEST MONTH_OF_YEAR_NAME AS '' ROWS 'Apr' OVER 'May' OVER 'Jun' OVER 'Jul' OVER 'Aug' OVER 'Sept' OVER 'Oct' OVER 'Nov' OVER 'Dec' OVER 'Jan' OVER 'Feb' OVER 'Mar'

ON Fiscal_Year RECOMPUTE
ON UNIT RECOMPUTE

WHERE ( UNIT EQ &UNIT.(OR(FIND FACT_SELECTED_CMG.UNIT IN FACT_SELECTED_CMG)).UNIT:. );

This message has been edited. Last edited by: MartinY,

Thanks yes it does need to be a compute it works as a compute.