How to show top 5 Vendors in the List Box

[Anonymous]

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.

[Anonymous]

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

[swuehl · Accepted solution]

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)

[hariprasadqv]

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.

or

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

[Colin-Albert]

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.

[Anonymous]

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

[jduenyas]

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

[hariprasadqv]

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