Calculating Rank - DMR package

[nikfalla]

Hello!

I am trying to determine the rank of a member and then based on that get the 3 members below and 3 members above that member.
For example, using Sales (Analysis) package I want to know the rank of staff as far as their quantity of sales is concerned, and once I find the rank of this specific staff, I want to know their 3 lower and higher competitors.

My package is DMR, and we are on Cognos 10.2.2.

I have attached the example I created. Your help is much appreciated.

Kind regards,
Nikta

[karthik.kadambi]

Hi Nikta,

You'll have to play around with MDX set of functions. http://www.ibm.com/developerworks/data/library/cognos/page82.html

I would just do the rank using [Rank] = rank([Quantity] within [Staff])
Based on this value, I would do a filter([Staff], [Rank] between ([Rank]-3) and ([Rank]+3))

I haven't done MDX for a while. You can also check out topCount() and bottomCount() functions.

[bvk.cognoise]

Hi nikfalla,

Please try the attached xml its working as per your requirement.

Regards
BVk