# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor II

## Table "Top N" limitation order with multiple criteria?

I'm using the "Top/Bottom N" limitation feature on a table as a ranking based on a measure.

My issue is that this specific measure causes a lot of ties, and I want to use a second measure as a tiebreaker for this ranking - is this possible?

Tags (3)
1 Solution

Accepted Solutions
Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi devarasu07‌,

I ended up using the solution in this thread, adding a very tiny fraction of my second measure to the original one. Ranking in case of tie breaker with respect to other metrics

6 Replies
Honored Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi,

yes, can you share mock data and expected output in excel format.

Thanks,

Deva

Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi Dev,

Attached the sample data and desired table.

I noticed that Qlik sorts by the second measure automatically if you add it to the table, but I'm not 100% sure.

Honored Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi,

try below method,

1) try to create calculated measure and suppress null value for this Cal.dimension

=IF(Aggr(Rank(SUM(Rating),4),NroProposta)<=5,NroProposta)

2)  using set analysis

SUM({<NroProposta = {"=Rank(SUM(Rating),4)<=5"}>}[Vl Operacao])

3) using set analysis with sort

aggr( SUM({<NroProposta = {"=Rank(SUM(Rating),4)<=5"}>}[Vl Operacao])

, (NroProposta, (=Sum({<NroProposta>}[Vl Operacao]), DESC)))

Honored Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi,

another method, using back end script

```Fact:
NroProposta, Rating, Vl Operacao
1522094, 24.40, 72000.00
1522122, 24.40, 13000.00
1522317, 19.15, 1075986.00
1522416, 24.40, 320000.00
1522534, 10.20, 425700.00
1522565, 10.20, 14800.00
1523181, 12.50, 171465.64
1523332, 12.50, 203220.00
1523637, 24.40, 50000.00
1523758, 36.70, 26800.00
1523794, 6.10, 179095.50
1523868, 24.40, 20000.00
1523912, 24.40, 72000.00
1524017, 6.10, 633420.00
1524220, 10.20, 16560.00
1524336, 24.40, 480000.00
1525419, 6.10, 1035000.00
1525986, 10.20, 12150.00
1526283, 24.40, 31600.00
1526368, 24.40, 30400.00
1531750, 10.20, 11200.00
1531752, 12.50, 259292.00
];
NoConcatenate
Temp:
Load NroProposta, SUM(Rating) as Rating,sum([Vl Operacao]) as [VI Operacao] Resident Fact Group By NroProposta;

NoConcatenate
Final:
First 5
Load NroProposta, Rating,[VI Operacao]  Resident Temp Order By Rating Desc;
Drop Tables Fact, Temp;
```

Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi Deva,

I didn't mention that "Rating" is already a set analysis expression - I tried substituting SUM(Rating) with my measure, but I couldn't figure out the syntax for methods 2 and 3.

Here is the expression for Rating:

Sum({\$<IndAprovacao={'S'}>} PdDesembolso * VlOperacao)

/

Sum({\$<IndAprovacao={'S'}>} VlOperacao)

And here are the results from the first method - the highlighted values should still take priority in the top 5.

Thanks,

Contributor II

## Re: Table "Top N" limitation order with multiple criteria?

Hi devarasu07‌,

I ended up using the solution in this thread, adding a very tiny fraction of my second measure to the original one. Ranking in case of tie breaker with respect to other metrics