Monday, February 20, 2012

MDX Newbie : Average Population Size

SQL 2005 SSAS

I have a table of daily population of vessels by category. (We're a shipping company so population is number of vessels, but the MDX could equally apply to any population).

I need to get the MDX to give average population size over a period and category. Total population was easy : it's just COUNT DISTINCT vesselname.

But average needs to be something like :
(COUNT DISTINCT DAILY vesselname for given category) averaged over the time period.

Can anybody point me in the right direction to get the MDX for this?

Thanks

Heres' a sample Adventure Works query which computes the average daily customer count for various product categories, in Q4 2003 ([Measures].[Customer Count] is a distinct count measure on the CustomerKey of the Internet Sales fact table):

>>

With

Member [Measures].[AvgDailyCustomers] as

Avg(Existing [Date].[Date].[Date],

[Measures].[Customer Count])

select {[Measures].[Internet Order Quantity],

[Measures].[Internet Sales Amount],

[Measures].[Customer Count],

[Measures].[AvgDailyCustomers]} on 0,

Non Empty [Product].[Category].Members on 1

from [Adventure Works]

where [Date].[Calendar].[Calendar Quarter].&[2003]&[4]

Internet Order Quantity Internet Sales Amount Customer Count AvgDailyCustomers
All Products 13,590 $4,009,218.46 5,090 60
Accessories 9,009 $175,035.18 4,212 49
Bikes 2,378 $3,751,923.02 2,313 26
Clothing 2,203 $82,260.26 1,745 20

>>

No comments:

Post a Comment