Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to count top 10 elements (by amounts)

Hi,

I got a table which holds alphanumeric product numbers (1000+), storage location and the respective amount in stock for that location in each line. I generate a diagramm which shows the overall amount in stock for each product. So the product number is my dimension and Sum(amount_in _stock) is the diagram value.

I sort the diagram to show the biggest amounts first and limit it to show the top 10 products (biggest amounts) only.

Here is the question: How do I get the total amount for all the products in stock for the products shown in this top 10 list only?

I need this value (and the toal number of all products in stock) to show up in another diagram that shows how many percent of all products are shon in this top 10 diagramm.

Regards,
George

1 Solution

Accepted Solutions
jsn
Master
Master

Hi George,

Technically you should be able to use the following expression in a text object if the dimension name is Product and the original expression is sum(amount_in_stock):

=sum(aggr(if(rank(sum(amount_in _stock))<=10,Sum(amount_in _stock)), Product))

This should sum up the 10 products with the highest sum(amount_in_stock). If two products have the same sum(amount_in_stock) they will share the ranking number though. Please look up the rank() function in the F1 help if you need to tweak it further.

View solution in original post

4 Replies
Not applicable
Author

You need to sort your dimensions by Y axis ( go in Sort table of the chart expression).

In Max number values, enter 10 in order to keep the top 10 values.

Rgds,

Sébastien

Not applicable
Author

Yes, sure. But how can I get the total amount (value summed up for all columns) in this top 10 list - as a variable preferably? I need that toal in another diagram.

Regards,

George

jsn
Master
Master

Hi George,

Technically you should be able to use the following expression in a text object if the dimension name is Product and the original expression is sum(amount_in_stock):

=sum(aggr(if(rank(sum(amount_in _stock))<=10,Sum(amount_in _stock)), Product))

This should sum up the 10 products with the highest sum(amount_in_stock). If two products have the same sum(amount_in_stock) they will share the ranking number though. Please look up the rank() function in the F1 help if you need to tweak it further.

View solution in original post

Not applicable
Author

Great, that is exactly what I was looking for!

Thanks,

George