Skip to Content
0

Limit the number of rows incase of Ranking Tie

Oct 14, 2016 at 07:56 PM

51

avatar image

Hi ,

Below is my attached table where few of the records are getting same Rank.

My Objective is to limit the number of records to TOP 5 rows only always ..So that in case of ranking tie the table should display first top five records only.

I have tried this by solutions given by other SAP community members, but none of them worked.i,e i tried creating a runningcount(rank;(rank)) but that is also not generating correct output . I am posting this message here again as that question is archived now .

please find attached pic

Thanks,

Charu

capture5.jpg (22.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Dinesh Babu Mar 31, 2017 at 10:40 AM
0

Hi Charu,

I have hit the similar issue recently. But mine is slightly different. I had a cross tab with one dimension in the column header another in the row header with a measure in the body of the table. Attached example data for your reference.

I have managed to resolve it by some work around. Work around is as follows based on your scenario

Main thing to achieve this is by some how we need to make the measure as unique values on which we apply ranking. So here the trick is

Create Variable 1

Syntax: =Asc([Partused])+Asc([Partused];2;1)+Asc([Partused];3;1) . This will give you a summed numeric value for the first three characters of the dimension string.

Variable 2

Syntax: =([Defect Qty]*1000)+Variable 1

Variable 3

Syntax: =Rank([Variable 2];[Partused])

Apply block level filter as below

Variable 3 <=5 --> This will be static. Incase if you want to make it dynamic , then use this variable as the input control and allow users to enter the value.

rank-and-tie.jpg

Please try and let me know the results!

Note: This will work only if the first three characters of the dimension string is different which has tie in some ranking. Also Ranking and data order will have circular dependency so aggregates doesn't work as expected. (Eg., Running Count, Running Sum , Line Number Etc.,)

Best regards,

Dinesh


rank-and-tie.jpg (58.9 kB)
Share
10 |10000 characters needed characters left characters exceeded