topic Re: How to show top 5 Vendors in the List Box in QlikView

How to show top 5 Vendors in the List Box

Anonymous — Mon, 23 Feb 2015 18:08:33 GMT

Pls look at the below excel sheet.

If I want my list box to show only top 5 Vendors based on Sales how can I do that.

Re: How to show top 5 Vendors in the List Box

Anonymous — Mon, 23 Feb 2015 18:10:20 GMT

It does not have to be a list box, as long as the box can contain list of Vendors, that is Qlikable. Some one suggested Input box but I am not sure how that works.

Thank you

Re: How to show top 5 Vendors in the List Box

swuehl — Mon, 23 Feb 2015 18:17:28 GMT

Try a field expression in your list box (<expression> in the drop down in field list on general tab):

=aggr( if(rank(sum(Sales))<=5,Vendor), Vendor)

Re: How to show top 5 Vendors in the List Box

hariprasadqv — Mon, 23 Feb 2015 18:29:35 GMT

Aggr function and rank function will help in that kind in expression. or assign expression to a variable and use it with dollar expansion in expression of the particular field expression.

load data from source with restrictions using sql syntax to load top five values.

Re: How to show top 5 Vendors in the List Box

Colin-Albert — Mon, 23 Feb 2015 18:35:45 GMT

As suggested, add a field expressions using RANK and AGGR.

Just remember to add the expressions to the Field Expression box - shown in Yellow below, and not the Column Expressions shown in Red.

To open the Field Expressions, click the down arrow on the filed box and select <Expression> from the list.

Re: How to show top 5 Vendors in the List Box

Anonymous — Mon, 23 Feb 2015 19:39:42 GMT

Thank you every one. I was using if(AGGR(rank(sum(sales),Vendor)<=5, Vendor) & it did not worked.

Swuehl suggested =aggr( if(rank(sum(Sales))<=5,Vendor), Vendor) and it worked.

Can you pls explain why. I was following the youtube link which I find to be correct. Please comment.

https://www.youtube.com/watch?v=l14j-5xNCIc

Re: How to show top 5 Vendors in the List Box

jduenyas — Mon, 23 Feb 2015 22:15:15 GMT

If this is your complete statement then it has an error:

if(AGGR(rank(sum(sales),Vendor)<=5, Vendor)

(Count the number of opening and closing parenthesis)

Besides what are you IF'ing here? Are you IF'ing the AGGR statement?

You should AGGR the result of the IF statement as Swuehl suggested.

(Makes sense?)

Re: How to show top 5 Vendors in the List Box

hariprasadqv — Tue, 24 Feb 2015 04:56:32 GMT

Aggr will return a table and Rank will return a value. And your expression is having a syntax error.