INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MDX & distinct count & date ranges

MDX & distinct count & date ranges

(OP)
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:

CODE

     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

CODE

     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...

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

RE: MDX & distinct count & date ranges

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

CODE

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

CODE

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

Syntax is probably off but the concept should work.

RE: MDX & distinct count & date ranges

(OP)
Hi MDXer,

The code I ended up with was:

CODE

    [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

RE: MDX & distinct count & date ranges

(OP)
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:

CODE

      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...

CODE

{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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close