Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikdash
Valued Contributor

How to show top 5 Vendors in the List Box

Pls look at the below excel sheet.

Calculated dimension.PNG

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

1 Solution

Accepted Solutions
MVP
MVP

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

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)

7 Replies
qlikdash
Valued Contributor

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

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

MVP
MVP

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

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
Contributor III

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

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.

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

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.

ListboxExpressions.JPG

qlikdash
Valued Contributor

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

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
Contributor III

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

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
Contributor III

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

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