Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
william_fu
Creator II
Creator 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?

1 Solution

Accepted Solutions
william_fu
Creator II
Creator II
Author

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

Thank you for your time.

View solution in original post

6 Replies
devarasu07
Master II
Master II

Hi,

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

Uploading a Sample

Thanks,

Deva

william_fu
Creator II
Creator II
Author

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.

devarasu07
Master II
Master II

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)))

devarasu07
Master II
Master II

Hi,

another method, using back end script

Fact:

LOAD * INLINE [

    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;

Capture.JPG

william_fu
Creator II
Creator II
Author

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.

ranking.PNG

Thanks,

william_fu
Creator II
Creator II
Author

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

Thank you for your time.