Variance of weighted average value

[jcao0000]

I have a report showing the margin (profit/sales) value. The table looks like
*******************************************************
DEPT ID WK 25 WK26 ...
1000 Y07 12.01 14.00
Y06 11.82 13.00
VAR 0.19 1.00
1001 Y07 12.00 13.20
Y06 11.50 13.00
VAR 0.50 0.20
...
*******************************************************
The margin value is shown in a cross tab:
Row is Dept ID
Column is WK.
Summary field is Margin value.

I have a command object to retrieve profit and sales value from the database.
Then I use a formula to caluate the margin. (In this case, I have two formula, one is to calculate Y07 margin, and the other is Y06)

However, now I need to consolidate several departments. For example DEPT 1= DEPT1000 + DEPT 1001 + DEPT 1002.

I changed the Summary field to be weighted average of Margin with Sales value. What formula I can use to show the variance of Y07 and Y06? Or any other approach I can take?

Thanks in advance for the help

 

[Madawc]

You could do a higher-level group that combined the departments you wanted to lump, e.g.

if {dept} in ["DEPT1000", "DEPT 1001", "DEPT 1002"]
then "DEPT 1" 
else ....

Or you could do running totals that accumulate values with a weighted average for a formula,

{dept} in ["DEPT1000", "DEPT 1001", "DEPT 1002"]

If you're not already familiar with Crystal totals, see FAQ767-6524.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10