asked on

Unique Count and Unique Count to Date (MDX)

Hello, can anybody indicate the MDX to calculate Unique Count to date ?

Having two Periods

Period Unique Count Unique Count to Date
First 10 10
Second 4 Unique Count (First + Second)

So if 2 of the 4 items are contained in the first period, then the unique
count should be 10 + 2 = 12

Thanks in advance,

BulZeyE

are the periods signified by integers? I need much more information. Table structures, PK and FK please..

gflorez

ASKER

This is just one large fact table and the date/period is defined as datetime type which is used to create a time dimension (year-week). Then I have an integer to find the unique occurences of an event within each week.

Date EventType
2/2/2003 4
2/2/2003 5
2/9/2003 6
2/9/2003 4
2/21/2003 6

So going back to my question:

Date Count Unique for Period Count Unique To Date
2/3/2003 2 2
2/9/2003 2 3
2/21/2003 1 3

So for the week of 2/21/2003, the eventid # 6 was already counted for the week of 2/9/2003 so it should not be counted the next time it occurs.

Thanks

gflorez

ASKER

Now, if it helps ... I can recreate the fact table from the following tables structure:

EventDesc Events

EventID EventID
EventStarts EventDate
Description EventLocation

The PK is EventID, right now, there are many eventdates and I need to count only the first occurence in my cube.

ASKER CERTIFIED SOLUTION

BulZeyE

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial