How to count top 10 elements (by amounts)

[#1]

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

[#2]

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

[#3]

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

[Anonymous · Accepted solution]

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.

[#5]

Great, that is exactly what I was looking for!

Thanks,

George