Rank to Limit only to 20 Values

building reports webintelligence
1

I have a table and Chart both having Ranking for Top 20 Budgeted Departments. Sometimes there may be 50 departments which have same budget in that case the table expands and overlaps with other blocks below. To avoid this I wanted to show only 20 departments maximum even when there is a tie. How do we achieve this inside a Webi?

Thanks in advance.

Diana_baker (BOB member since 2005-03-18)

2

Hi,

The entire table chart depends up on one of the column like any ICD-description. So, you have to rank the data busing this column and moreover to ignore the other columns you have to format. Go to the properties tab and click on the column and set the borders, colors and data format and Background so that for testing save as in PDF then you can validate accordingly… Hope this helps…

hsurapan (BOB member since 2009-08-30)

3

Sorry it is not clear on how to implement your solution.

Currently I have Charts and Table that are ranked Top 20 inside Webi report using the Webi Ranking feature --> Rank Top 20 Department (dimension) by Budget (Measure).

When the ranking is unique, then there is no problem. The issue is when their is a tie in the rank. Say 50 departments have same budget, I still want to limit the count to 20 rather than showing all 50.

Thanks…

Diana_baker (BOB member since 2005-03-18)

4

Hi,

Then you insert a break and then you do the Ranking…

hsurapan (BOB member since 2009-08-30)

5

This is has been discussed here before and as I asked back then, when there is a tie which of the “tied” rows do you decide to drop technically unless there is something else that also determines ranking how are you proposing to randomly select 20 of 50 departments with the same budget ?

Nniixx :australia: (BOB member since 2009-09-02)

6

This might help.

Prashant Purohit :india: (BOB member since 2009-02-18)

7

No it won’t.

The orginal poster already mentioned she is ranking by top 20 but if 50 departments all have the same budget they will be ranked in the top 20.

She only wants to display 20, hence my question around which she should illiminate.

Nniixx :australia: (BOB member since 2009-09-02)

8

Thanks for your replies.
Yes, You are correct. Even. when 50 Departments has same budget, the users want to see only 20 (May be alphabetically ordered or Random 20 out of the 50 is okay too).
They definitely do not want to see all 50 when there is a tie because the chart becomes too crowded and table extensions can overlap with other table/charts.

Thanks,
Raj

Diana_baker (BOB member since 2005-03-18)

9

This won’t but it was the post I mentioned earlier

Nniixx :australia: (BOB member since 2009-09-02)

10

If you are only doing all this to avoid the overlapping problem then why don’t you simply use “Relative Position” for your tables and charts?

  • Noman Jaffery

Noman Jaffery :pakistan: (BOB member since 2005-05-26)

11

Thanks Noman, that is a good idea!

Diana_baker (BOB member since 2005-03-18)

12

Well that was the obvious answer, but I thought you quite literally only wanted to display 20 rows rather than avoiding the overlap of blocks.

Oh well.

Nniixx :australia: (BOB member since 2009-09-02)

13

I know this is an old post but what to do if I only want to show the top 10 rows. if there is a tie it should only show one of them dont really mind. I checked the board but cant find a solution to that.

thanks
deross

deross :ireland: (BOB member since 2010-10-16)

14

Hello Deross,

Were you able to find solution to the top 10 or top 20 ranking with the tie issue? I was also getting the same issue. I was looking for Top 10 and ended up getting 11 as there was a tie in 10th position. I looked over forum for quiet some time and was not able to find a solution.

Thank you guys.

arun9911 :us: (BOB member since 2008-01-16)

15

I know of no way of doing this :), in BO or SQL.

As has been asked, time and time again, how are you supposed to choose which of these tied records to drop?

Mak 1 :uk: (BOB member since 2005-01-06)

16

Hi Mak 1,

Its been said to me that I can always go ahead and choose the #10 position which ever is first. The data in here is alphabetically arranged.

Thanks for your time Mak 1.

arun9911 :us: (BOB member since 2008-01-16)

17

Hello Guys,

This is how I acheived the solution.
Remember this solution is specific to my requirement.
In my requirement, no matter what I should only get top 10. If for example, at position 10 there are 4 different dimension values, I can show any of the four values as my #10 as it does not really matter which one I show. Main reason being all tied at #10.

I created a variable called rank.

Rank = =Rank([Measure]+(RunningCount([Measure])/ (RunningCount([Measure])+0.8) )).

BTW, tht is not a smiley. Its number 8. I dont know why smiley replaces #8.
And then,
I added this as a block filter " Where [Rank] < 11.

I works for me now.

I tried different data sets.

Thanks guys

arun9911 :us: (BOB member since 2008-01-16)

18

Good work.

Now that you worked it out, out of curiousity couldn’t you have built the rank for top 10 that gave you multiples for 10th then block filtered on RunningCount[Measure] < 11 ?

Nniixx :australia: (BOB member since 2009-09-02)

19

Hi if you want to simply restrict.
Add a new column.
Keep the function "LineNumber"
As it starts from 0. Restrict it to display till 19 only
And hide the column that you have inserted using ranking procedure or simply make the borders,text and background as white color.

rajasekhar_852 :india: (BOB member since 2010-07-01)

20

Hi,

Please follow the below steps to do this.

  1. Create a variable in WebI report

  2. add a condition like below
    e.g. if rank(column name) <= 20 then true else false

  3. Drag and drop the variable in webi report.

  4. Refresh the report and see.

I hope this will work for you.

Regards,
Dhandapaani

dhandapaanikm (BOB member since 2010-09-10)