topic How to sort a top N when using calculated dimension? in QlikView

How to sort a top N when using calculated dimension?

Mon, 08 Feb 2010 16:33:14 GMT

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.

How to sort a top N when using calculated dimension?

sathishkumar_go — Mon, 08 Feb 2010 19:15:10 GMT

Please find the attachements.

I think it ll helpful for u.

Regards

Sathish

How to sort a top N when using calculated dimension?

Mon, 08 Feb 2010 21:54:03 GMT

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.

How to sort a top N when using calculated dimension?

Tue, 09 Feb 2010 10:03:57 GMT

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

How to sort a top N when using calculated dimension?

deepakk — Tue, 09 Feb 2010 10:42:51 GMT

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

How to sort a top N when using calculated dimension?

Tue, 09 Feb 2010 10:56:00 GMT

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.

How to sort a top N when using calculated dimension?

deepakk — Tue, 09 Feb 2010 11:48:25 GMT

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

How to sort a top N when using calculated dimension?

Tue, 16 Feb 2010 09:40:22 GMT

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.