Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

BadBO (TechnicalUser) (OP)
2 Oct 08 12:42
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 (MIS)
2 Oct 08 13:00
What do you want the output to look like.

Steve Krandel
Intuit

BadBO (TechnicalUser) (OP)
2 Oct 08 14:30
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 (TechnicalUser)
3 Oct 08 4:10
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 (TechnicalUser) (OP)
3 Oct 08 5:17
I managed to do this by creating a new measure 'rank' using the rank formula.

many thanks for your suggestions.
skrandel (MIS)
3 Oct 08 10:42
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 (TechnicalUser) (OP)
3 Oct 08 10:54
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 (MIS)
4 Oct 08 13:06
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close