Rank on grouping
Recently I got a problem of ranking based on a group.
Lets try to solve the same problem using Adventure Works cube.
We have details of customer country and customers. We want to rank them based on their internet sales. But we want the rank to be reset when the country changes.
In general rank will assign rank in ascending order , but we want to reset it when the country changes.
We have created a set [myset] for customer and country sorted on [Internet Sales Amount] and we will asign them a rank.
WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank]} ON 0 ,
[myset] ON 1
from [Adventure Works]
Here we can see the rank is in ascending for order for all customers irrespective of country. We want to reset this rank when the country changes. So we will create a new member
[Measures].[newrank]
WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
MEMBER [Measures].[newrank] AS
iif([myset].ITEM([Measures].[rank]-1).ITEM(0).NAME <> [myset].ITEM([Measures].[rank]-2).ITEM(0).NAME ,
1,([myset].ITEM([Measures].[rank]-2), [Measures].[newrank])+1
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank], [Measures].[newrank]} ON 0 ,
[myset] ON 1
from [Adventure Works]
Recently I got a problem of ranking based on a group.
Lets try to solve the same problem using Adventure Works cube.
We have details of customer country and customers. We want to rank them based on their internet sales. But we want the rank to be reset when the country changes.
In general rank will assign rank in ascending order , but we want to reset it when the country changes.
We have created a set [myset] for customer and country sorted on [Internet Sales Amount] and we will asign them a rank.
WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank]} ON 0 ,
[myset] ON 1
from [Adventure Works]
Here we can see the rank is in ascending for order for all customers irrespective of country. We want to reset this rank when the country changes. So we will create a new member
[Measures].[newrank]
WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
MEMBER [Measures].[newrank] AS
iif([myset].ITEM([Measures].[rank]-1).ITEM(0).NAME <> [myset].ITEM([Measures].[rank]-2).ITEM(0).NAME ,
1,([myset].ITEM([Measures].[rank]-2), [Measures].[newrank])+1
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank], [Measures].[newrank]} ON 0 ,
[myset] ON 1
from [Adventure Works]
4 comments:
Hi Amish,
Thank you for the great MDX code about Rank with partitioning - MDX that you have on your blog (http://shahamishm.blogspot.com/2010/03/rank-with-partitioning-mdx.html). I just have a question... If I want to do the same as what you did, ranking customer country and customers based on their internet sales. Although I don't need to reset them when country changes, I need to add another dimension to the query. I'd like to know product category sold to each customer. Instead of having one line/row for a customer, now you have multiple rows, depending on the products that you sold to each of these customers. How would you do modify your existing MDX code by adding product category to the query but still be able to keep the ranking based on the total internet sales that you orginally?
Thank You so much for your help!
try this
WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank]} ON 0 ,
nonempty([myset]*[Product].[Product Categories].[Category],[Measures].[Internet Sales Amount]) ON 1
from [Adventure Works]
Hi Amish
Can you please suggest me equivalent MDX Query for below SQL Query.
Right now i'm using below query in sql view for my cube but i prefer to keep same in cube using calculated measure instead of using sql view.
SELECT [OrganisationID]
,[ApplicationID]
,fact.[FinancialAccountID]
,[ReportingPeriodID]
,[PeriodStartDateID]
,[PeriodEndDateID]
,[CurrencyID]
,[RevenueRecognitionBasisID]
,[RevenueReportingBasisID]
,[GroupID]
,[TurnoverTotal]
,SUM([TurnoverTotal]) OVER(partition by [OrganisationID],dim.[FinancialYearEndDateID] ORDER BY [OrganisationID],[PeriodEndDateID]) AS [FYTD_TurnoverTotal]
,[GrossProfit]
,SUM([GrossProfit]) OVER(partition by [OrganisationID],dim.[FinancialYearEndDateID] ORDER BY [OrganisationID],[PeriodEndDateID]) AS [FYTD_GrossProfit]
FROM [fct].[TEMPFinancial] fact INNER JOIN
[dim].[tempFinancialAccount] dim
ON fact.FinancialAccountID=dim.FinancialAccountID
Thanks
PC
thanks, it is working
Post a Comment