topic Sort a calculated dimension in a bar chart in QlikView

Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 15:59:00 GMT

Hello,

I need your help if possible. I have created a bar chart that uses a calculated dimension for the X-axis. The calculated dimension is:

=if([Gross Sales]<10000000 ,'<$10MM',

if(([Gross Sales]>=10000000 and [Gross Sales]<25000000),'$10MM-$25MM',

if(([Gross Sales]>=25000000 and [Gross Sales]<50000000),'$25MM-$50MM',

if(([Gross Sales]>=50000000 and [Gross Sales]<75000000),'$50MM-$75MM',

if(([Gross Sales]>=75000000),'>$75MM','None')))))

The Y-axis shows the number of products, so the bar chart shows correct values. It shows the number of products for each Gross Sales range. The problem is that I am not able to sort the view.

I want the X-axis to show in this order (ascending):

'<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM'

I am getting a different order, see the attached screenshot.

I have tried the following:

match(

if([Gross Sales]<10000000 ,'<$10MM',

if(([Gross Sales]>=10000000 and [Gross Sales]<25000000),'$10MM-$25MM',

if(([Gross Sales]>=25000000 and [Gross Sales]<50000000),'$25MM-$50MM',

if(([Gross Sales]>=50000000 and [Gross Sales]<75000000),'$50MM-$75MM',

if(([Gross Sales]>=75000000),'>$75MM','None'))))), '<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM')

But doesn't work. Any help will be very appreciated.

Thanks,

Re: Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 16:04:32 GMT

Have you tried putting your expression as a variable, say vSalesCat?

Then you could do match($(vSalesCat),'<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM').

Re: Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 16:16:24 GMT

I have tried your suggestion and still doesn't work. The bars change of position when I change from ascending to descending but still not in the order wanted.

Thanks,

Re: Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 16:25:51 GMT

Hmm. Is Gross Sales a calculated field? If you replace it with sum(ALL sales) or whatever the calculation is in the variable, that could possibly help... unfortunately, I don't know for sure.

Re: Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 16:30:50 GMT

Hi,

Go to the sort tab in your chart and highlight the calculated dimension and promote it to the top of the list. Select sort by 'Expression'. Select 'Ascending' from the drop down and then type [Gross Sales] in the expression box. Hope that helps!

-ART

Re: Sort a calculated dimension in a bar chart

Thu, 28 Feb 2013 18:45:34 GMT

This fixed it. Thank you very much.