Tuesday, October 26, 2010

MDX Top N



MDX query to show the Top 5 products sold for last 3 years using Rank Function. 


WITH
     
      SET [OrderedSet] AS
      Generate
      (
            {
                  STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]").LAG(3)
                  :
                  STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]")
                  },
            ORDER
            (
                  NonEmpty
                  (
                        (
                              [Date].[Calendar].CurrentMember,
                              [Product].[Product].[Product]
                        ) ,
                        { [Measures].[Sales Amount] }
                  ),
                  [Measures].[Sales Amount], BDESC
            )
      )

      MEMBER [Measures].[Rnk] AS
      Rank
      (
            (
                  [Date].[Calendar].CurrentMember,
                  [Product].[Product].CurrentMember
            ),
            Exists
            (
                  [OrderedSet],
                  {[Date].[Calendar].CurrentMember}
            )
      )

SELECT
      {[Measures].[Sales Amount], [Measures].[Rnk]} on 0,
      FILTER([OrderedSet],[Measures].[Rnk]<=5) on 1
FROM
      [Adventure Works]

MDX query to show the Top 5 products sold for last 3 years using TopCount Function. 


WITH SET Years AS
{
     
      STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]").LAG(3)
      :
      STRTOMEMBER("[Date].[Calendar].[Calendar Year].&[" +VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]")
}


SET Top5 AS
Generate(Years,
      TopCount(Years.CurrentMember * [Product].[Product].[Product], 5,
            [Measures].[Sales Amount])
      )


select {[Measures].[Sales Amount]} on 0,
Top5 on 1
from [Adventure Works]

When to us Rank approach?

Suppose you want to get the Top 5 products and not just the Top 5 rows, then you might want to consider the Rank approach. 
Example:
Product1  $100   Rank=1
Product2  $90     Rank=2
Product3  $80     Rank=3
Product4  $80     Rank=3
Product5  $70     Rank=4
Product6  $60     Rank=5
Product7  $50     Rank=6

Rank approach will return you Product1,2,3,4,5,6
Top count approach will return you Product1,2,3,4,5


Well today Nov/06/2010,


The exists function here would rank the same value as different. There was requirement where the same value measure value should show same ranks and also one should be able to rank the products per year basis. (i.e. rank based on multiple dimension members)



WITH
SET [OrderedSetPerYr] AS         
Generate
(
Date].[Calendar].[Calendar Year].members ,
ORDER
(
NonEmpty
(
(
[Date].[Calendar].CurrentMember,
[Product].[Product].[Product].Members
),
{ [Measures].[Internet Sales Amount] }
)
,
[Measures].[Internet Sales Amount], DESC
)
)
     
MEMBER [Measures].[Rnk] AS
Rank
(
(
[Date].[Calendar].CurrentMember,
[Product].[Product].CurrentMember
),
NonEmpty
(
(
[Date].[Calendar].CurrentMember,
[Product].[Product].[Product].Members
),
{ [Measures].[Internet Sales Amount] }
)
,[Measures].[Internet Sales Amount]
)

SELECT
      {[Measures].[Internet Sales Amount],[Measures].[Rnk] } on 0,
      [OrderedSetPerYr]  on 1
FROM  [Adventure Works]






No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

LinkWithin

Related Posts with Thumbnails