locked
How to use 2 different Measures on 2 different Time Dimensions? RRS feed

  • Question

  • What I am trying to do it get the total Premium for Calendar MTD based on a measure I created (comm_prem - new_bus).   I also need Premium for Calendar PY MTD - but I dont' want to use the same measure, I need to just use comm_prem.
    Then when I have these 2 amounts, I would want a Percent Growtch.

    I figured this might have to be done via MDX.  


    This is what I get:
                                             Existing Bus                                        Comm_premium
                                             Calendar MTD      Calendar PY MTD      Calendar MTD            Calendar PY MTD
    Sales Rep 1    Oct 2009         I WANT THIS       Don't want this         Don't want this          I WANT THIS

    Since the Hirearchy is selecing Calendar MTD and Caleandar PY MTD, which I need both, but only want to display Calandar MTD for Existing_Bus and Calendar PY MTD for Comm_Prem 


    This is how the MDX looks via ProClarity. Note the Member Existing Business is the one that needs to be calculated differntly.

    SELECT

     

    NON EMPTY { { { [<##<MEMBER!svoth!Existing Business>##>], [Measures].[Commissionable Premium] } * { [Written Date].[Date Calculations].[Calendar MTD], [Written Date].[Date Calculations].[Calendar PY MTD] } } } ON COLUMNS ,

     

    NON EMPTY { { { [Agency].[Sales Rep].[All Agencies].CHILDREN } * { [Written Date].[Calendar].[Month].&[200910] } } } ON ROWS 

     

    FROM [Sales]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

    Any help?

    Tuesday, February 2, 2010 9:15 PM

All replies

  • One way to accomplish this would be to build to calculations in ProClarity, and then place those on columns.

    For example:

    Existing Business Calendar MTD = ({MDX for existing business}, [Written Date].[Date Calcs].[Calendar MTD])

    Calendar PY MTD = ([Commisionable Prem], [Written Date].[Date Calcs].[Calendar MTD])

    Then place the two calcs above on columns, and you should see the information you want.
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 11, 2010 9:19 PM