CODEWITHSUNDEEP
ssasmdx
Roxana
Roxana

Reputation: 91

Fetching Previous Member while using order function in MDX

I have a mdx query which returns "productCode" and "Product share of total Sales" as a result.Also i need that query be sorted on "Product share of total Sales" (Decs) So I came up with following MDX Query 

WITH MEMBER [Measures].[Contribution] AS
     Format(
           IIF(
              IsEmpty([Measures].[Detail_Net_Sales]),
              0,
              [Measures].[Detail_Net_Sales]
              )/
           [Measures].[SumTotalPayable] 
           )
SELECT  
      {[Measures].[Contribution]} 
   ON COLUMNS,
      Order(
           [DIMProduct].[ProductCode].[ProductCode].AllMEMBERS,
           [Measures].[Contribution],
           BDESC
           ) 
   ON ROWS 
FROM [Model] }

the problem is when i also want to have Previous Contribution for each productcode i wrote down somthing like this :

WITH MEMBER [Measures].[Contribution] AS
Format(iif(IsEmpty([Measures].[Detail_Net_Sales]),0,[Measures]     [Detail_Net_Sales])/[Measures].[SumTotalPayable] )
MEMBER [Measures].[test]
AS

([Measures].[Contribution], [DIMProduct].[ProductCode].CurrentMember.PrevMember)

SELECT  { [Measures].[Contribution] ,[Measures].[test]} ON COLUMNS
, Order([DIMProduct].[ProductCode].[ProductCode].AllMEMBERS ,[Measures].    [Contribution],BDESC)ON ROWS 
FROM [Model]

but the above code will return previous Contribution without desire order. do you have any idea how can i fix this?

Edit : I want to have the Contribution of the previous row base on my sort and also have the test measure base on my prior sort,like the below table:

ProductCode Contribution Test                       
----------- ------------ ----                                            
123          17.56       null
332          17.30       17.56
245          16          17.30
656          15.90       16

but what i get is like this :

ProductCode Contribution Test                       
----------- ------------ ----                                            
123          17.56       17.30
332          17.30       16
245          16          Null
656          15.90       17.30

Upvotes: 2

Views: 1180

Answers (1)

FrankPl
FrankPl

Reputation: 13315

WITH MEMBER [Measures].[Contribution] AS
     Format(
           IIF(
              IsEmpty([Measures].[Detail_Net_Sales]),
              0,
              [Measures].[Detail_Net_Sales]
              )/
           [Measures].[SumTotalPayable] 
           )
     SET SortedProducts AS
     Order(
           [DIMProduct].[ProductCode].[ProductCode].AllMEMBERS,
           [Measures].[Contribution],
           BDESC
           ) 
     MEMBER [Measures].[PrevContribution] AS
     (SortedProducts.Item(
           Rank([DIMProduct].[ProductCode].CurrentMember, SortedProducts) - 2)
           .Item(0), 
     [Measures].[Contribution]) 
SELECT  
      {[Measures].[Contribution], [Measures].[PrevContribution]} 
   ON COLUMNS,
      SortedProducts
   ON ROWS 
FROM [Model] }

Your definition of the test measure uses the order of members as defined in the cube (alphabetically, if you did not define a custom order). But you need the order by Contribution, so I defined that as a named set containing the members in this order. Then I mis-used the Rank function which gives you the position of a tuple in a set. As the rank of the first member is 1, and I use the Item function to get a tuple within the set - which starts numbering the tuples with zero -, and we have to go one member back, you need the - 2, and finally, we need another Item(0) to get a member from the tuple.

Upvotes: 1

Related Questions