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