Does anyone know how to calculate a standard deviation measure using the
mean of another population(parent dimension set)?

Example:
Facility 'XYZ' has 5 patients (base table in sql server database) with the
following ages in years: (10,20,30,40,50)
Using standard deviation logic, "SQRT( (Sum(x-u)^2) / Count)", where u = 30
(the average of the 5 patients in the set) we get Stddev=14.14
But I would like to use the average age of the patients in the facilities
parent dimension level which is u = 50 giving me Stddev = 24.49.

I have successfully implemented the following, but this uses the set
average and NOT the parent average:
1) load in a simple SUM measure (x)
2) load in a simple SUM measure of x squared (x2)
3) load in a counter measure called cnt.
4) Then using the formula for stdev, create a measure in the cube as
follows: ((x2 - ((x^2)/cnt))/cnt)^0.5

I am using AS 2000 which works off a sql server 2000 database.

Thanks,

_________________________

Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
dan_zaccarine (AT) hanfordbay (DOT) com
http://www.hanfordbay.com
___________________________

Assuming a [Facilities] dimension, here's an approach using some
intermediate calculated measures:

'[Measures].[x]/[Measures].[cnt]'

Member [Measures].[avgp] as
'([Measures].[avgx], [Facilities].Parent)'

Member [Measures].[varx] as
'([Measures].[x2] / [Measures].[cnt])
- ([Measures].[avgp] * ((2 *
[Measures].[avgx]) - [Measures].[avgp]))'

Member [Measures].[sigx] as
'[Measures].[varx] ^ 0.5'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Your algorithm worked perfectly.

Thank YOU!!!
Dan
___________________________

Daniel J. Zaccarine
Hanford Bay Associates, Ltd.

http://www.hanfordbay.com
___________________________
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote