Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using 'Rank' formula in Business Objects

Status
Not open for further replies.

BadBO

Technical User
Oct 2, 2008
10
GB
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!
 
What do you want the output to look like.

Steve Krandel
Intuit
 
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
 
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

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

many thanks for your suggestions.
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top