Print Page - Calculating Rank

Title: Calculating Rank - DMR package
Post by: nikfalla on 09 Aug 2015 10:47:09 AM

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

Title: Re: Calculating Rank - DMR package
Post by: karthik.kadambi on 10 Aug 2015 09:29:44 PM

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.

Title: Re: Calculating Rank - DMR package
Post by: bvk.cognoise on 11 Aug 2015 07:12:04 AM

Hi nikfalla,

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

Regards
BVk

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: nikfalla on 09 Aug 2015 10:47:09 AM