topic Chart Query - Sorting Dimensions Manually in QlikView

Chart Query - Sorting Dimensions Manually

stuwannop — Thu, 02 May 2013 11:38:17 GMT

Hi All

Can anyone help me here - I was wondering if there was a way to sort dimension values in a chart manually as opposed to the default chart options (A-Z, Load Order Etc). So if I had a list of "Regions", North, South, East, West, Scotland, Midlands - how would I present the sales totals (purely for example) in a chart where I could put the regions in any order I please?

Thanks

Stu

Re: Chart Query - Sorting Dimensions Manually

alexandros17 — Thu, 02 May 2013 11:44:09 GMT

you can sort by expression but this depends on chart type

Re: Chart Query - Sorting Dimensions Manually

Thu, 02 May 2013 11:45:47 GMT

Hi Stuart

In the document properties you can set the dimension to sort by using an expression. For example:-

=IF([NCC.Responsibility Center]='MIDLANDS',1,

IF([NCC.Responsibility Center]='NORTH EAST',2,

IF([NCC.Responsibility Center]='NORTH',3,

IF([NCC.Responsibility Center]='NORTH WEST',4,

IF([NCC.Responsibility Center]='SCOTLAND',5,

IF([NCC.Responsibility Center]='SOUTH',6))))))

I hope this helps. If you need any further help then please do not hesitate to contact me.

Get In!!

Re: Chart Query - Sorting Dimensions Manually

giakoum — Thu, 02 May 2013 11:47:22 GMT

you can add an addtional field named order and order by that or you can manipulate the region names to order them as desired :

1. North, 2. South, 3. East, 4. West, 5. Scotland, 6. Midlands

Re: Chart Query - Sorting Dimensions Manually

stuwannop — Thu, 02 May 2013 11:51:20 GMT

Thanks both - both seem like good things to try

Cheers

Re: Chart Query - Sorting Dimensions Manually

giakoum — Thu, 02 May 2013 11:52:51 GMT

having a nested if for sorting is not a good idea. it is memory consuming and affects perfomance. What if a new region is added?

Re: Chart Query - Sorting Dimensions Manually

stuwannop — Thu, 02 May 2013 12:00:46 GMT

If I created a new field in the script assigning a number to the region would that be better? If I did that how would I then show that in the chart? By chart I mean a simple sum table. I don't want Qlik to display 1. North, 2.Midlands etc as there is other stuff I need to sort.

Re: Chart Query - Sorting Dimensions Manually

giakoum — Thu, 02 May 2013 12:11:55 GMT

much better.

you would use that new field in the sort by expression in the chart. if you have multiple rows with the same region, you probably would use min([Order]) and not simply [Order]