MDX example: Calculate the sales ranking of each product in each quarter

Damon _ Li, a user of the ITPUB data warehouse and data mining forum, asked: Count the sales rankings of various products in each quarter of the current year (now there are dates, product dimensions, and sales measurements) the following figure shows the Q1Q2Q3Q4 sales ranking: Product ranking: 130002200035000140

Damon _ Li, an ITPUB data warehouse and data mining forum user, asked: Count the sales rankings of various products in each quarter of the current year (now there are dates, product dimensions, and sales quotas) the figure below shows Q1 Q2 Q3 Q4 sales volume ranking product 1 3000 2 2000 3 5000 1 40

The ITPUB data warehouse and data mining forum user Damon _ Li asked:

StatisticsVariousProductIn this yearEachQuarterOfSalesRanking, (Now there are dates,ProductDimension andSalesQuota) is shown as follows:
Q1 Q2 Q3 Q4
SalesAmountRanking SalesAmountRanking SalesAmountRanking SalesAmountRanking
Product1 3000 2 2000 3 5000 1 4000 1
Product2 4000 1 3000 2 4000 2 2000 3
Product3 2000 3 4000 1 3000 3 3000 2
.......
Be familiar with the instructions of the MDX predecessors and provide ideas.

MDX was launched more than 10 years ago. Although I am not engaged in this field now, it is easy to see this problem.

In SSAS 2008 R2 and SQL Server [Adventure Works],ExampleDatabase (downloaded in Codeplex (ExampleMedium,QuarterAt the 2nd Level of the time dimension ):

StatisticsVarious ProductIn this year Each QuarterOf Sales Ranking. MDX

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH MEMBER [Measures].[SalesRank] AS RANK( [Product].[Product Categories].currentmember, [Product].[Product Categories].[AllProducts].children , [Measures].[Sales Amount] ) SELECT {DESCENDANTS([Date].[Calendar].[CalendarYear].&[2007],2)} *{[Measures].[Sales Amount],[Measures].[SalesRank]} ON0, [Product].[Product Categories].[AllProducts].childrenON1 FROM [Adventure Works]

The following is the execution in SSAS. You can set [Date]. [Calendar]. [Calendar Year]. & [2007] use [Date]. [Calendar]. replace CurrentMember, and specify the year [Date] In the WHERE statement. [Calendar]. [Calendar Year]. & [2007], which is placed in the query slice to specify the query conditions in the slice.

StatisticsVariousProductIn this yearEachQuarterOfSalesRanking"Src =" http://cdn.verydemo.com/upload/2013_06_20/13716877427240.jpg "width =" 760 "height =" 416 ">

StatisticsVariousProductIn this yearEachQuarterOfSalesRanking