Using 'Rank' formula in Business Objects

[BadBO]

I have a crosstab in BO and I wish to select the Top 3 values from the measure in the table. I have tried using the rank function but this does not work.

My crosstab looks something like this:

Name A B C D
Date
01/01 10 8 2
02/01 7
03/01 1 9 6 2

I know I need to use the ‘rank’ formula but I cannot seem to get it to work properly.

Any help greatly appreciated!

 

[skrandel]

What do you want the output to look like.

Steve Krandel
Intuit

 

[BadBO]

Basically the report is lots of pages. I want just the name columns with the top 3 results for each date, all other results filtered out.

Thanks for your help

 

[blom0344]

The crosstab columns are not 'independent'. They are basically the result of the intersection (and rollup of the measure). So your requirement is going to collide with the way a crosstab works. If you want the rank to work for each A,B,C,D you would want a set of tables..

Ties Blom

 

[BadBO]

I managed to do this by creating a new measure 'rank' using the rank formula.

many thanks for your suggestions.

 

[skrandel]

Sorry for being obnoxious here, but.....

All I asked was for a simple example of what you wanted. If you are going to post a problem, it helps to post the desired result.

Then, when you finally solve the problem on your own, it is VERY valuable to the other members of the community if you post your solution. Boards likes this are all about sharing. So, please share your solution.

Steve Krandel
Intuit

 

[BadBO]

Point taken...

Once Blom0344 pointed out the crosstab columns were not independent (Thanks Blom!) I realised I needed to create a new measure which ranked the original measure in my crosstab.

Using the variable editor I added the formula =Rank([measure];[Name];[Date])

I then applied a filter to the block filtering on my new 'rank' measure with the operator 'in list' with the values 1,2 & 3 to select only the top 3.

I hope this is of use to someone.

 

[skrandel]

That's a good one. You must be on XI R2 (or higher) to do this. In the past you couldn't filter on the Rank.

Steve Krandel
Intuit