Get above member value in mdx in ordered selection

Question

I need to get previous (above) member's Sales using mdx query for each current member.

The following mdx works fine for bringing previous member value in normal query, but if I set to order the rows by Sales to get a top, the previous value is mixed-up and it dosn't follow the ordered members.

WITH
    MEMBER [Measures].[PrevMemberSales] AS 
      'SUM(
         [Markets].[Country].CurrentMember.PREVMEMBER
       , [Measures].[Sales]
       )'
SELECT
    NON EMPTY 
        ({[Measures].[Sales], [Measures].[PrevMemberSales]}) ON COLUMNS,
    NON EMPTY 
        //ORDER(
            {[Markets].[Country].Members}
        //    , [Measures].[Sales], BDESC) 
        ON ROWS
FROM [SteelWheelsSales];

I need to get the previous member value following to top, to get the difference between Sales. I am using Mondrian 3.

Eliza M Eliza M · Accepted Answer · 2016-07-25T12:28:04

I just managed to solve the problem by using the following formula for calculated member:

WITH
    MEMBER [Measures].[PrevMemberSales] AS 
       (Order(
          [Markets].[Country].Members
         ,[Measures].[Sales]
         ,BDESC
        ).Item(
                Rank(
                  [Markets].[Country].CurrentMember
                   ,Order(
                      [Markets].[Country].Members
                     ,[Measures].[Sales]
                     ,BDESC
                   )
                ) - 2
               ).Item(0)
        , [Measures].[Sales]
       )
SELECT
    NON EMPTY ({[Measures].[Sales], [Measures].[PrevMemberSales]}) ON COLUMNS,
    NON EMPTY 
        ORDER(
            {[Markets].[Country].Members}
            , [Measures].[Sales], BDESC) 
        ON ROWS
FROM 
    [SteelWheelsSales]

The result is OK but my next question is to make a calculated member that brings the sum of ALL Sales for all the above members. For example, for France Sales the value should be USA+Spain sales, for Australia Sales I need USA+Spain+France Sales.

Any idea?

Get above member value in mdx in ordered selection

1 Answers