Ranking without duplicates in Business Objects

Here's a trick I got from James on the BOB-forum -- to a question from one of the visitors. Personally, I found the question a bit dodgy, but James worked out a nice two-step to get around the problem:

Lets asume we wanted to have a Top 10. But we wanted to disregard any duplicate values.


I'll get us a list of data to illustrate the idea:

In the above list, a bunch of duplicates exist. K = 9, but L is also 9 (we want to lose L in our top 10), G is 6 but H is also 6, we want to lose H.. etc.

The first step is to create a ranking variable [Rank]:

=Rank([Value];[Code])

Great, now we want to find out what the rank is of the previous line:

=Previous([Rank])

Nice, so now, when Previous and Rank are the same, we want to lose the line. So we create a Flag-variable:

=if([Rank]=[Previous Rank];0;1)

Watch out though, don't insert the variable into your table or:

And if you get that error, you'll have to close the document and re-open it to get this to work.

Next, we apply a filter on the table:

Now, if we apply a top 5 on the table, it will only rank the unique values.

Works like a charm.

Thanks James,

Peter