Rank function : similar amount showing same rank. How to choose only one of them

[nsaxena]

Hi All,

In my list, I have two similars amount which come up with the same rank and i want to take only one out of that. What should i do in cognos to achieve same.Currently it is showing both dimension,but i need to show only one. It could be any.
I am using rank( fact for dimension) and then i am using rank=1 as where clause filter.

[Lynn]

Is this a relational package? The rank function is a relational function. For dimensional you'd use TopCount.

The rank function does Olympic ranking. You'll need to decide which wins in case of a tie. In the example below would you take the first country alphabetically or the last or use some other criteria to decide? An aggregate function of minimum or maximum on the data item for your dimension might suffice although it sounds a bit random to me. I think a second related metric is a better approach.

Code Select Expand


value  rank  country
  10    1       A
  10    1       L
  15    3       H
  32    4       P


This article has some techniques that might help you, but you still need to understand the requirement as to which one wins in case of a tie. It is not a recent article and I've not looked at rank recently so not sure if there is something newer that might give you other options.

http://www-01.ibm.com/support/docview.wss?uid=swg21339207

[nsaxena]

Hi Lynn...i referred the solution mentioned in zip file. It worked. Cheers!!

Many thanks!