Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mjmilian
New Contributor II

Need bottom 20 results

I have an expression:

Sum([Net Cash])

I need to only show the lowest 20 results of this expression in a table based on the dimension [ID].  I tried this expression, but it brought in the lowest 20 IDs.  I want it to sort by ID based on the 20 lowest Net Cash values.

if(aggr(rank(Sum([Net Cash])),[ID])<=20,[ID])

Qlik Forums Bottom 20.GIF

15 Replies
MVP
MVP

Re: Need bottom 20 results

May be try this

Aggr(If(Rank(-Sum([Net Cash])) < 21, [ID]), [ID])

mjmilian
New Contributor II

Re: Need bottom 20 results

Thanks Sunny but that still shows the value as the ID # not the Net Cash Value

Qlik Forums Bottom 20.1.GIF

MVP
MVP

Re: Need bottom 20 results

May be try this

Aggr(If(Rank(-Sum([Net Cash])) < 21, Sum([Net Cash])), [ID])

isingh30
Contributor III

Re: Need bottom 20 results

May be this -

= only({<Net < {439}>}ID)

OR

= IF(Net < 439,'ID','Net')

mjmilian
New Contributor II

Re: Need bottom 20 results

Ishtdeep,

Thanks for the reply, but the data is always going to be updated.  So those values are only good as of today.  Tomorrow they will change.

mjmilian
New Contributor II

Re: Need bottom 20 results

Sunny,

Both expressions are giving me null answers when in actuality there sum(net cash)  is on the right.

Qlik Forums Bottom 20.2.GIF

MVP
MVP

Re: Need bottom 20 results

Would you be able to attach your qvf file?

mjmilian
New Contributor II

Re: Need bottom 20 results

Unfortunately I cannot.

MVP
MVP

Re: Need bottom 20 results

Mocked up data to show the issue?