MDX Top 10 based on a Column

Question

I'm currently working with MDX in pentaho. Currently I'm facing issue is that Unable to get top 10 count based on a Measure.

i.e., I want to write MDX query for find top 10 customer's based on Current year Sales volume and also needed Previous year sales amount for that customers.

My Problem is that MDX query Sum the Previous and Current year sales volume and listed out the top 10.

How can i get top 10 based on Current year column?

For Example,

Customer   Current Year   Previous Year
A           100           200
B           150           125
C           200           -

Expected Result:

 Customer   Current Year   Previous Year
  C           200           -    
  B           150           125
  A           100           200

Please help me out.

nsousa nsousa · Accepted Answer · 2014-05-06T10:18:23

The Topcount function takes 3 arguments, a Set, the number of items to return and a numeric expression.

Supposing you have [Customer].Members on Rows, two [Time] dimension members already defined and [Measures].[Sales] as your measure, you can use

Select
  TopCount( [Customer].Members, 3, ( [Measures].[Sales], [Time].[Current Year]) ) on Rows,
  { [Time].[Current Year], [Time].[Previous Year] } on Columns
From [My Cube]
Where [Measures].[Sales]

The TopCount will return the top 3 customers, based on their rank by

([Measures].[Sales], [Time].[Current Year])

If you don't specify this tuple as the numeric expression you get the rank by the overall [Measures].[Sales] value.

Documentation: http://technet.microsoft.com/en-us/library/ms144792.aspx

MDX Top 10 based on a Column

1 Answers