Sign in
 

none
Rank function

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have an MDX Query that is using the rank function, but it is not resetting the rank value when the top level of the hierarchy changes.  I want to use MDX to get the same values I would get when using the TSQL Row_Number () over (partition by ...) function.  Here is a sample of the actual output and desired output from the query below:

    HourNum HighLowDiff HighLowDiffMode(actual) HighLowDiffMode(desired)
    0 0 2 2
    0 1 1 1
    0 2 3 3
    1 0 4 1
    1 1 6 3
    1 2 5 2 


    WITH SET HourDiff AS 
Order (([Dim Date Time].[Hour Num].Children,[Gbp Usd Gaps H1].[High Low Diff].Children),[Measures].[High Low Diff],DESC)

MEMBER [Measures].[High Low Diff Mode] AS
Rank (([Dim Date Time].[Hour Num].CurrentMember,[Gbp Usd Gaps H1].[High Low Diff].CurrentMember), HourDiff)

SELECT
{	[Measures].[High Low Diff Mode]	} ON COLUMNS,
{	([Dim Date Time].[Hour Num].Children,[Gbp Usd Gaps H1].[High Low Diff].Children)	} ON ROWS
FROM h1


    Monday, November 18, 2013 2:29 PM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Would you be opposed ordering the set inside the rank function?  This makes it a bit easier because it keeps the current context in play for the outside dimension (the one you want to "partition" on).

    Here's an example where the months of each year are sorted and ranked by sales amount.  The ranking is "partitioned" by year. 

    WITH
	SET [YearMonthSet] AS
		GENERATE(
			[Date].[Calendar Year].[Calendar Year],
			ORDER(
				CROSSJOIN (
					 [Date].[Calendar Year].CurrentMember
					,[Date].[Month of Year].[Month of Year]
				)
				,[Measures].[Internet Sales Amount]
				,DESC
			)
		)
	MEMBER [Measures].[Rank in Year] AS
		RANK(
			 [Date].[Month of Year].CurrentMember
			,ORDER (
				 [Date].[Month of Year].[Month of Year]
				,([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])
				,DESC
			 )
		)
SELECT
	{
		[Measures].[Internet Sales Amount],
		[Measures].[Rank in Year]
	} ON 0,
	{
		[YearMonthSet]
	} ON 1
FROM
	[Adventure Works]

    The calculated set isn't necessary but just makes the result set pretty:

    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by JeffreyShumaker Wednesday, November 20, 2013 12:54 AM
    Tuesday, November 19, 2013 9:39 PM
    |