none
mdx ratio of parent report

    Question

  • Hi Guys,

    I’d like to calculate the percentage ratio of dimension members but… it should be time dynamic …

    What I have so far…

    with member [Measures].[RatioToParent] As

    (

    Sum (PeriodsToDate ( [Times].[Timeline].[Year ID], [Times].[2010-09-03] ),[Measures].[Encounter Count])

    )

    /

    (

    [Measures].[Encounter Count],

    [Hosp].[Hospital].[ALL]

    ), Format = "Percent"

    select

    {[Measures].[Encounter Count],

    [Measures].[RatioToParent]}

    on columns,

    [Hosp].[Hospital].[Hosp].members on rows from

    [Hpm Reporting]

     

    The query above should show for each hospital the ratio of the patient encounters form 01-01-2010 to 09-03-2010.  The issue I have is the denominator

    [Measures].[Encounter Count],

    [Hosp].[Hospital].[ALL];

     The way the query above is written the denominator includes all the encounters for all times and that doesn’t makes sense. I’d like the total encounters over all hospitals also from 01-01-2010 to 09-03-2010…I tried different options to accomplish this but nothing worked so far…

    Any hint into the right direction would be much appreciated…

    Thx a bunch

    Friday, September 17, 2010 3:07 AM

Answers

  •  

     

     The way the query above is written the denominator includes all the encounters for all times and that doesn’t makes sense. I’d like the total encounters over all hospitals also from 01-01-2010 to 09-03-2010…I tried different options to accomplish this but nothing worked so far…

     

     

     

     


    Ludwig,

     

    Looks to me that you're trying to compose a YearToDate calculation for 2010. I suggest you switch to using Ytd and set it up step by step to prevent putting in the same constraint various times.

    Maybe you can take this sample, based on AW,  as a start. I first declare the selected timeframe and then refer to it in both nominator and denominator.

    with 
    
    set [timeframe] as ytd ([Date].[Calendar].&[20060731])
    
    member [measures].[nominator] as 
    Sum ([timeframe],
    ([Promotion].[Promotion Type].currentmember,
    [Measures].[Reseller Sales Count])), format= "#,#.00"
    
    
    member [measures].[denominator] as 
    Sum ([timeframe] ,
    ([Promotion].[Promotion Type].[All],
    [Measures].[Reseller Sales Count])) , format= "#,#.00"
    
    
    
    member [Measures].[RatioToParent] As 
    [measures].[nominator] / [measures].[denominator] 
    , Format = "Percent"
    
    select
    {
    [measures].[nominator] ,
    [measures].[denominator] ,
    [Measures].[RatioToParent]} 
    
    on columns,
    
    nonempty([Promotion].[Promotion Type].[Promotion Type].members, [measures].[nominator]) on rows 
    
    from [Adventure Works]
    
    

     

    with this resultset, similar to what you request:

     

         nominator denominator RatioToParent
    No Discount  1,244.00 1,415.00 87.92%
    Seasonal Discount  98.00 1,415.00  6.93%
    Volume Discount  73.00 1,415.00  5.16%
    

     

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, September 17, 2010 9:27 AM

All replies

  • Hi,

    Try using the range operator in the where clause...

    something like this

    with member [Measures].[RatioToParent] As

      (

      Sum (PeriodsToDate ( [Times].[Timeline].[Year ID], [Times].[2010-09-03] ),[Measures].[Encounter Count])

    )

     /

    (

     [Measures].[Encounter Count],

      [Hosp].[Hospital].[ALL]

     ), Format = "Percent"

    select {[Measures].[Encounter Count],

    [Measures].[RatioToParent]} on columns ,

     [Hosp].[Hospital].[Hosp].members on rows from

    [Hpm Reporting]

    where {[Date].[Calendar].[Date].&[01-01-2010] : [Date].[Calendar].[Date].&[09-01-2010]}

     

    Regards

     Gokul

     

    Friday, September 17, 2010 4:23 AM
  •  

     

     The way the query above is written the denominator includes all the encounters for all times and that doesn’t makes sense. I’d like the total encounters over all hospitals also from 01-01-2010 to 09-03-2010…I tried different options to accomplish this but nothing worked so far…

     

     

     

     


    Ludwig,

     

    Looks to me that you're trying to compose a YearToDate calculation for 2010. I suggest you switch to using Ytd and set it up step by step to prevent putting in the same constraint various times.

    Maybe you can take this sample, based on AW,  as a start. I first declare the selected timeframe and then refer to it in both nominator and denominator.

    with 
    
    set [timeframe] as ytd ([Date].[Calendar].&[20060731])
    
    member [measures].[nominator] as 
    Sum ([timeframe],
    ([Promotion].[Promotion Type].currentmember,
    [Measures].[Reseller Sales Count])), format= "#,#.00"
    
    
    member [measures].[denominator] as 
    Sum ([timeframe] ,
    ([Promotion].[Promotion Type].[All],
    [Measures].[Reseller Sales Count])) , format= "#,#.00"
    
    
    
    member [Measures].[RatioToParent] As 
    [measures].[nominator] / [measures].[denominator] 
    , Format = "Percent"
    
    select
    {
    [measures].[nominator] ,
    [measures].[denominator] ,
    [Measures].[RatioToParent]} 
    
    on columns,
    
    nonempty([Promotion].[Promotion Type].[Promotion Type].members, [measures].[nominator]) on rows 
    
    from [Adventure Works]
    
    

     

    with this resultset, similar to what you request:

     

         nominator denominator RatioToParent
    No Discount  1,244.00 1,415.00 87.92%
    Seasonal Discount  98.00 1,415.00  6.93%
    Volume Discount  73.00 1,415.00  5.16%
    

     

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, September 17, 2010 9:27 AM
  • Gokul and Cees,

    Thx a bunch for your valuable input. I got it to work based on adding the where clause...I like Cees approach...very clean and structured...Thx again L

    Friday, September 17, 2010 5:06 PM