15 Replies Latest reply: May 31, 2018 1:14 PM by Matt Milian

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

• ###### Re: Need bottom 20 results

May be try this

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

• ###### Re: Need bottom 20 results

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

• ###### Re: Need bottom 20 results

May be try this

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

• ###### Re: Need bottom 20 results

Sunny,

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

• ###### Re: Need bottom 20 results

Would you be able to attach your qvf file?

• ###### Re: Need bottom 20 results

Unfortunately I cannot.

• ###### Re: Need bottom 20 results

Mocked up data to show the issue?

• ###### Re: Need bottom 20 results

Here is an excel sheet with the exported data from qlik for that table.

• ###### Re: Need bottom 20 results

May be this -

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

OR

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

• ###### 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.

• ###### Re: Need bottom 20 results

Can't you use the built in functionality of the table dimension to just set the limit to return bottom 20?

• ###### Re: Need bottom 20 results

Dan,

That does not work because on any given day, some IDs might not have any data, so that just gives me either no data selected or zero values. This is what happens when I say to put a fixed limitation on the bottom 20.

• ###### Re: Need bottom 20 results

can you uncheck the "include null values" and in your data script set the 0's to be null?

• ###### Re: Need bottom 20 results

They are unchecked.