# 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

###### Who is Participating?

Commented:
Try this:

create table table1 (datefield datetime, eventid int)

insert into table1
(datefield, eventid)
select
'2/2/2003',                      4
union all
select
'2/2/2003',                       5
union all
select
'2/9/2003',                       6
union all
select
'2/9/2003',                     4
union all
select
'2/21/2003',                     6

select t2.datefield,
t2.datecount,
count(distinct t1.eventid)
from table1 t1
inner join (select datefield, count(distinct eventid) datecount
from table1
group by datefield) t2
on t1.datefield <= t2.datefield
group by t2.datefield, t2.datecount
order by 1

0

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

0

Author Commented:
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

0

Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.