Chart Query - Sorting Dimensions Manually

[stuwannop]

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

[alexandros17]

you can sort by expression but this depends on chart type

[#3 · Accepted solution]

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!!

[giakoum]

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

[stuwannop]

Thanks both - both seem like good things to try

Cheers

[giakoum]

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?

[stuwannop]

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.

[giakoum]

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]