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

The Second best

Again, on the BOB forum, I stumbled into this question.

This person wanted to show the second highest value in a list. The following procedure will allow you to pick which one you want to show.

The formula I will be using here is Rank

This is my starting table. I want to show only the second best of each year.
I create a variable, using the rank function

=Rank([Sales revenue])

This one won't cut it, because, when you filter on it, it will actually show you Q2 2004. Because the filter will actually operate in the context of the report.

The correct function is therefore :

=Rank([Sales revenue];Top;([Year]))

This will show the rank of each quarter, for each year. Next, we need to set a filter on that :

 

There, only the second best shows. Yes, the rank-value now shows 1 everywhere, since the item that still shows is now the only one to still be available.

To solve that one : 

=NoFilter(Rank([Sales revenue];Top;([Year])))

Now, it shows the number 2.

Enjoy,

Peter