Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX - SSAS, getting result count of last measurement within a period Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 1:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 5:58 AM
Points: 3, Visits: 17
Hi I am having trouble trying to write some MDX code.
I am trying to get the last measurement result pr. person within a period. I have the following measurements and results:

Person      Measurement  result              Date
1 1 overweight 2011-01-01
1 2 Normal 2011-03-01
2 3 Underweight 2011-01-01
2 4 Underweight 2011-02-01
2 5 Normal 2011-03-01
1 6 overweight 2012-01-01



Right now I am only able to get a normal count on measurements and distinct count on persons. So I can create a table like this (Full period):
           Number of persons            Number of measurements
Overweight 1 1
Normal 2 3
Underweight 1 2



But I would like to have only the last category within my chosen period, so it looks like this (Full period):

            Number of persons        Number of measurements
Overweight 1 1
Normal 1 1
Underweight 0 0


But it should also be able to create a table like this for the period (year 2011)

            Number of persons        Number of measurements
Overweight 0 0
Normal 2 2
Underweight 0 0


My dimensions and measurements are like the following:
[Measures].[Numb persons]
[Measures].[Numb measurements]
[Registrationdate].[Date]
[Person].[ID]
[result].[Name]


I hope someone can help me
thanks in advance

Br
Jens H
Post #1427234
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse