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