PF attached.

I believe you wish to select the 5 lowest absolute numbers, so it might be useful to add a formula field ABS(number), replacing the 'number' with the expression from the [Selected Ratings] field, then limit the number of displayed values in this field.

RANK function can be used with or instead, depending on the requirement, selecting the descending order, so the values -36, -46 have higher rank value than -500, in order to select them in your top 5.
RANK(value, field, isAscending, includeNulls, dataSubset)

It would be great if you have an example for us to filter based on top 5 and bottom 5 as we tried the functions as suggested but couldn't get the result as we expected.

Thanks & regards
Rajesh B

Rajesh - Attached is a general solution to filtering and ranking top/bottom N subsets, and calculating sub-averages and sub-rankings.

Hi Thomas,
I have made some small changes to the data in your file. Please check the problem that we are facing here....Now it is returning more then 5 values with N set to 5?

As has been discussed on the phone with Paola, you have added many duplicate records to the values to be ranked. The RANK function does not recognise duplicates explicitly.

There is a difference between a RANK function and a DENSERANK function, as explained here:

http://forums.visokio.com/discussion/1079.

I have values like this only. Any workaround of this??

If you need the DENSERANK function you and all your colleagues should vote for it.

There is a workaround. Use javascript for ranking. It is 1 minute to write the code. Read values to array and return position+1 of given value. You will get DENSERANK this way.

There is workaround in Omniscope too, so I attach here modified file posted earlier. Use of variables "Top N" and "Bottom N" enables you to create dynamic results, updating the calculated table values, as well as the view titles. You can then hide empty values to focus on selected top/bottom results.
Formulas used:

Rank Desc
RANK([Data Values],[Data Values], false, false)  
 #ranking values in descending order, so the highest value has rank 1
 
One= 1
 
Running Total =
RUNNINGTOTAL([One], SUBSET([Rank Desc]))
#identifying shared rank values 
 
Dense Rank Desc =
RANK([Data Values], [Data Values], false, false,
SUBSET([Running Total],1,"="))
#ranking every Data value only once 
 
Dense Rank Asc=
RANK([Data Values], [Data Values], true, false,
SUBSET([Running Total],1,"="))  
#Ranking ascending unique values 
 
Top N values=
IF([Dense Rank Desc]<=[Top N],
"Top "+[Top N],
null)
#Variable "Top N" introduced to generate dynamic values
 
Bottom N values=
IF([Dense Rank Asc]<=[Bottom N],
"Bottom "+[Bottom N],
null)
#Variable "Bottom N" introduced to generate dynamic values  
 
View Header Titles:
"{{ SUBSET_FIRSTNONNULL([Top N val]) }} - Dynamic value"

"{{ SUBSET_FIRSTNONNULL([Bottom N val]) }} - Dynamic value"


Thank you Paola for your help on this. We are almost done with your help. Only one concern is pending here. Top 10 is returning 11 records and Bottom 6 is returning 12 records.

If we are talking about top 10 , It sould be 10 in count irrespective of repeated values. Same for bottom 6 also.

Please suggest.

Just use aggregate option in the view where you are displaying your results.

Quick way - use move/keep feature to first remove empty cells (those outside of Top N), then click on value 1 in the [Running Total] field to 'keep' only one record per rank value.

Alternatively use a formula:
IF(
AND([Dense Rank Asc]<=[Bottom N],[Running Total]=1), "Bottom "+[Bottom N], null)

Thanks Poala and really sorry if my Question was incomplete/not clear. I don’t have to select 6 records as ‘one from each value of dense rank if repeated’. I need it in same order. All 1, all 2 ,all 3 and so on till count is 6.

It might be best if you posted a spreadsheet (small sample) with data and desired results.

PFA

You should use the Sort block in the DataManager space so the records enter Omniscope ordered by your target field.
Add formulas to create running total for the top/bottom values:
Running total top=
IF([Top N val]<>null,
RUNNINGTOTAL([One], SUBSET([Top N val]),false),
null)
Running total bottom=
IF([Bottom N val]<>null,
RUNNINGTOTAL([One], SUBSET([Bottom N val])),
null)

Now you can use the filters to trim the selection to actual top 10 records, even if some of them share the same Rank value.
You can use either filters to customise your selection, or the visual selection on the Bar/line view, that orders the Rank values on X axis and cumulative record count on Y.
In the image here we can see that two records share rank 10. Because the stacking is done on 'per record basis', you will be able to select only one of the two.
(File is attached)