How to sort a top N when using calculated dimension?

[#1]

Hi,

I need to display the top N products by years in a bar chart and group the remaining products as 'other products'.

In order to do that, i'm using aggr() function in calculated dimension:

=aggr(if(rank(sum(Sales))<5, Product,'Other products'),Product)

This is working properly but the issue is that i'm not able to sort those products. I tried different formulas as sorting expression in the chart without any success...

Any idea on how to perform a sort here? (see the qvw attached)

Thanks in advance.

[sathishkumar_go]

HI

Please find the attachements.

I think it ll helpful for u.

Regards

Sathish

[#3]

I used the same approach for one of my tables. Is there a way to have the 'Others' bucket always display after the top N are sorted and displayed? This is the issue I am facing now.

[#4]

Hi,

It's easy to sort by Y-value when only one dimension is used. But my problem is more with the bar chart where "Year" is the first dimension and the aggr() dimension the second one.

In this case, the second dimension can be sorted using a specific "expression" but i'm not able to find the right formula. Whatever the expression i use, it has no effect on the product sorting in the bar chart.

Best Regards

[deepakk]

Hi Satish,

I have seen your application. How can I make the default 4 to a input from user.

When I am trying to use a variable its not working.

Deepak

[#6]

Hello,

In order to make the default 4 as an input, the only thing you need is to replace the 4 by the name of a variable in the calculated dimension expression. Then create an input box for this variable so that the user can change its value.

BR

[deepakk]

I got it... I was making a small error in setting the variable.

[#8]

Hi experts!

My problem is not solved. Any idea on how to sort when the second chart dimension is calculated dimension with aggr() function? (see bar chart in the initial post).

Thanks.