Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sid
Contributor II
Contributor II

Show top 10 products along with 'OTHERS'

I am analyzing the sales of products over a week. I have a chart where the week is plotted on the x-axis and the products are stacked by week and Sales is the measure.

I only want to show the Top 9 products + 'Others' overall by sales in the chart. Using limitations doesn't work as the Top 10 is calculated across week first and then by product whereas I don't want week to be considered in the calculation.

I used the below expression in the dimension calculation for product.

=if(aggr(Rank( Sum(Sales)),Product)<=10,Product,'Others')

Let's say I have 22 products. The first time, I should see the top 9 products with the next 13 grouped as 'Others'. When I click on Others, I should then see the next Top 9 products with the next 4 grouped under 'Others'. When I click a third time, I see only 4 products because these are the only ones under Others.

The Top 10 works correctly but when I click on 'Others' in the legend, it basically works correctly only the first time but the second time, it ignores the grouping and calculates the top 9 based on the excluded values from the first time and so on and basically it becomes a loop where it always calculated top 9 and the remaining 13 values and it goes on and on.

Can someone please help me with this?

 

Labels (3)
1 Solution

Accepted Solutions
sid
Contributor II
Contributor II
Author

For anyone looking for a solution, I managed to solve it by changing the above expression to the below one

=aggr(if(Rank( Sum(Sales))<=10,Product,'Others'),Product)

View solution in original post

1 Reply
sid
Contributor II
Contributor II
Author

For anyone looking for a solution, I managed to solve it by changing the above expression to the below one

=aggr(if(Rank( Sum(Sales))<=10,Product,'Others'),Product)