Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)