SSAS - Sum of Top or Bottom

Question

SELECT  
  [Measures].[Internet Sales Amount] ON COLUMNS  
 ,Tail  
  (  
    [Date].[Calendar Year].[Calendar Year].MEMBERS  
   ,2  
  ) ON ROWS  
FROM [Adventure Works];

Above MDX query gives me output as :

Year Internet Sales Amount

CY 2003 $9,791,060.30

CY 2004 $9,770,899.74

I understood how this query worked but I want to create Calculated measure in a cube which will always give me sum of bottom 2 years. How to do this? I am a newbie to SSAS. I am good at designing simple measures and dimensions but when it comes to using MDX, I am mostly stuck.

PS: I tried using TopCount, BottomCount etc but here I want to order by "Year", which is a dimension.

Any help would be appreciated.

Thanks,

Parry

gonsalu gonsalu · Accepted Answer · 2012-04-13T15:14:10

The following query calculates a measure for sum the the last 2 years of the Date dimension:

WITH
MEMBER [Measures].[Sales from the last 2 Years]
    AS Aggregate( Tail( [Date].[Calendar Year].[Calendar Year].Members, 2)
                , [Measures].[Internet Sales Amount]
                )

SELECT { [Measures].[Sales from the last 2 Years]
       } ON COLUMNS
     , { Tail( [Date].[Calendar Year].[Calendar Year].Members, 2)
       } ON ROWS
    FROM [Adventure Works]

Other interesting query, would be calculating a measure for the sum of each year and its previous:

WITH
MEMBER [Measures].[Sales from 2 years]
    AS Aggregate( { [Date].[Calendar Year].CurrentMember.PrevMember
                  : [Date].[Calendar Year].CurrentMember }
                , [Measures].[Internet Sales Amount]
                )

SELECT { [Measures].[Internet Sales Amount]
       , [Measures].[Sales from 2 years]
       } ON COLUMNS
     , NON EMPTY
       { [Date].[Calendar Year].[Calendar Year]
       } ON ROWS
    FROM [Adventure Works]

It does a sum, because the aggregation type of the measure [Measures].[Internet Sales Amount] is Sum, and the Aggregate function aggregates according to the measure's aggregation type.

MDX is a hard topic to grasp; if you're starting out, I recommend you read the book MDX Solutions, 2nd edition.

SSAS - Sum of Top or Bottom

1 Answers