topic Re: Limiting the amount of rows returned in a straight table in QlikView

Limiting the amount of rows returned in a straight table

cliff_clayman — Thu, 06 Oct 2016 18:03:16 GMT

I have a straight table that I would like to return only the Top 10 totals. How do I limit the results to the top 10 in descending order? I've attached a sample file that should help. I need it by Division, meaning I expect to see the top ten Supplier Names by Division.

Re: Limiting the amount of rows returned in a straight table

sunny_talwar — Thu, 06 Oct 2016 18:05:44 GMT

In a table box or straight table?

Re: Limiting the amount of rows returned in a straight table

cliff_clayman — Thu, 06 Oct 2016 18:08:45 GMT

Straight table. I just put in a table box just to display the data. There would normally be a Sum on the Total field.

Re: Limiting the amount of rows returned in a straight table

sunny_talwar — Thu, 06 Oct 2016 18:12:35 GMT

May be using this expression:

=If(Rank(TOTAL Sum(Total), 1, 1) < 11, Sum(Total))

Re: Limiting the amount of rows returned in a straight table

cliff_clayman — Thu, 06 Oct 2016 18:16:54 GMT

I guess I should have left my expression in as I am not sure how to implement the Rank function into it:

Sum({$<[FY]={$(vFY)}, [Country]={'US'}, [Flag]={'x'}>} [Total])

Re: Limiting the amount of rows returned in a straight table

sunny_talwar — Thu, 06 Oct 2016 18:18:36 GMT

May be like this:

=If(Rank(TOTAL Sum({$<[FY]={$(vFY)}, [Country]={'US'}, [Flag]={'x'}>}Total), 1, 1) < 11, Sum({$<[FY]={$(vFY)}, [Country]={'US'}, [Flag]={'x'}>}Total))

Re: Limiting the amount of rows returned in a straight table

cliff_clayman — Thu, 06 Oct 2016 18:24:09 GMT

This only gave me the Top Ten overall. I need the Top Ten for each Division.

Re: Limiting the amount of rows returned in a straight table

sunny_talwar — Thu, 06 Oct 2016 18:29:43 GMT

May be use dimension limits in that case

Expression will stay as your original expression

Sum({$<[FY]={$(vFY)}, [Country]={'US'}, [Flag]={'x'}>} [Total])

Re: Limiting the amount of rows returned in a straight table

cliff_clayman — Thu, 06 Oct 2016 18:34:13 GMT

That did it! Thank you. I thought I had to do that on the expression. I didn't realize it would work that way for a dimension.