MDX & distinct count & date ranges

[eo]

Hi

I am hoping that I am on the right track here. I have a SSAS 2005 cube. In rows I have a date attribute. The cells contains I want to show an accumulated distinct count of claim numbers. The following code shows an example of the basic dataset:

     Claim No
2004 A10
2004 A10
2004 A18
2004 A17 - Dist count of claim no = 3

2005 A17
2005 A18
2005 A21
2005 A29 - Dist count of claim no = 4

2006 A10
2006 A21
2006 A30
2006 A10 - Dist count of claim no = 3

But I need to accumulate these distinct claim numbers across the dates in a running total/ accumulated fashion. I.e. first across 2004, then across 2004 & 2005, and then across 2004 - 2006

     DistCount
2004 3 - A10, A17, A18
2005 5 - A10, A17, A18, A21, A29
2007 6 - A10, A17, A18, A21, A29, A30

I ideally need to create a calculated measure within SSAS 2005. I have been searching along the lines of the following code...

count(distinct ({null:[Date].[Date].CurrentMember},[Claim].[ClaimNumber]))

...you will notice that I do not have a measure in the syntax as I am rather trying to create one...am I on the worng track completely?

EO
Hertfordshire, England

 

[MDXer]

you are going down the right path just be sure that you seperate in your mind SQL and MDX while doing MDX you are better off totally forgetting SQL. MDX has a DistinctCount() Function. Also rather than

{null:[Date].[Date].CurrentMember}

try

{OpeningPeriod([Date].[Date]):[Date].[Date].CurrentMember}

Syntax is probably off but the concept should work.

 

[eo]

Hi MDXer,

The code I ended up with was:

    [MEASURES].[ClaimCount] =
        DISTINCTCOUNT ({OpeningPeriod([Date Transaction].[DateTransaction].CurrentMember):[Date Transaction].[DateTransaction].CurrentMember}, 
            , [Claim].[Claim Number Count])
            ;

But the error was "Too many arguments were passed to the DISTINCTCOUNT MDX function. No more than 1 arguments are allowed."

Any idea

EO
Hertfordshire, England

 

[eo]

Still no closer to an argument although I have now spent the whole day on this. I have a conventional measure which is a distinct count. This is done through taking the Claim Number in the fact table and creating a distinct count measure group. But off course this is what I will use to show non accumilated distict counts, such as the first example:

      ClaimNoDisCount
2004  3
2005  4
2006  3

Now I am not sure if I can or cannot use this measure in a further distinctcount calculated measure through MDX as my (ignorant) logic says that it has already been defined at some or other scope, so I will not be bale to provide a distinctcount which straddles multiple intersections, such as...

{OpeningPeriod([Date Transaction].[DateTransaction].CurrentMember):[Date Transaction].[DateTransaction].CurrentMember}

Furthermore a distinctcount cannot have more than a single argument. It requires DistinctCount (Set_expresion)??? I have run out of ideas

EO
Hertfordshire, England