# MDX Ranking and Ties with thresholds

### Pregunta

• Hi All,

I'm hoping someone can help me with this one. I have a real world example involving MDX ranking that I am trying to solve. I have created a query using Adventure Works that contains the essence of what I am trying to solve in my project. If anyone can help me with this I would appreciate it.

What I am trying to do is to come up with a ranking that involves logic for Ties. In my case a rank tie can occur when the difference of the measure between the current member and previous member of an ordered set is within a certain threshold (in this Adventure Works example say that the number 5 is the threshold value. How could I create a new member that would re-rank the ordered set based on this threshold. Using the third parameter to the Rank function gives me performance problems. Here is the query:

with

// order the set - get the top 30 for this example
set [CustsWithSales] as
topcount(order([Customer].[Customer].[Customer].members,[Measures].[Internet Sales Amount],BDESC),30)

// I chose the 2 parameter version of Rank() because adding the third parameter to determine rank took many minutes (I had to kill it). With this version I am
// relying on an ordered set so this query takes 3 to 4 secs
member [Measures].[Customer Rank - Orig] as
rank([Customer].[Customer].currentmember,[CustsWithSales])

// from this ordered set iterate over each member.
// Compare the current member to the prev member and if the difference in the Internet Sales Amt is <= 5 then mark it as a Tie with the prev member (i.e. give it the same rank as prev member)

select
{[Measures].[Internet Sales Amount],[Measures].[Customer Rank - Orig]} on 0,
non empty [CustsWithSales] on 1
from
(
select
{[Geography].[State-Province].&[CA]&[US]} on 0  // just California customers for now
)

jueves, 21 de junio de 2012 21:06

### Todas las respuestas

• hi,

your quest raises a theoretical question. your function, lets say: [ f(a,b) -- > rank(a)= rank(b)  if b-a < constant] is not transitive !

a = 2

b = 6

c = 9

rank(a)= rank(b)

rank(b)= rank(c)

f((a,c) ??

as an automated process, we have to handle all kind of possible chain equalities.

I suggest to change the rules to alleviate those ambiguities

Philip,

• Editado jueves, 21 de junio de 2012 23:03
jueves, 21 de junio de 2012 22:55