Thursday, March 22, 2012

calculated DistinctCount over two levels of "incomplete" weeks

Hello all,
I have a cube on as2005 where my Time dimension has 5 levels(year, month, quarter, week, day) and a measure that is a distinct count of customers (I also have a customer dimension, with 1 level: customer)

In my time dim, I have "incomplete" weeks, for example the week 32 exist on both august and september months, 5 days of the week in august and 2 days in september.
So at week level I get the distinct customers for that week, for example:
month | week | # distinct customers
AUG | 32 | 15
SEPT | 32 | 20

but, since the week is the same, i want to get the distinct customers for the whole week, and put it on the last month where that week appears, in the example let's say that for week 32 there are 18 different customers, I want a 18 on SEPT.

month | week | # distinct customers
AUG | 32 | NULL
SEPT | 32 | 18

Someone know how can I achieve this?
Thanks!I've found the way to do this, I hope this helps the community!

Aggregate
(
UNION([DM Time].[Hierarchy].CurrentMember.PrevMember.Children,[DM Time].[Hierarchy].CurrentMember.Children)
,[Measures].[# Customers]
)

only for the scope of the week.

No comments:

Post a Comment