topic Re: Multilevel sort on bar chart in QlikView

Multilevel sort on bar chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

This may look like a simple task but I can't seem to do it properly. Most of the materials I've found so far here are on custom sorts, but nothing on multilevel sort. Essentially, I want my bar chart to sort first by Country (Descending A>Z) then by Value (Descending).

Sample table below and I have attached the qvw file with the bar chart.

Location	Country	Value
AA	USA	150
BB	Canada	500
CC	USA	250
DD	Canada	400
EE	USA	300
FF	Canada	1000

Hoping for any advice. Thanks!

Re: Multilevel sort on bar chart

Chanty4u — Wed, 04 May 2016 06:23:03 GMT

is this?

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 06:27:12 GMT

Hi Chanty, in your file, the bars are sorted by Country but they are not sorted by Value as a secondary sort. The final look of the graph would have the bars in this order:

FF (Canada-1000) - BB (Canada-500) - DD (Canada-400) - EE (USA-300) - CC (USA-250) - AA (USA-150)

Re: Multilevel sort on bar chart

Kushal_Chawda — Wed, 04 May 2016 06:30:14 GMT

can you try this

First create the Sort Table

Sort:

LOAD distinct Country,

Autonumber(Country) as SortCountry

Resident YourTable

Order by Country desc;

Now go to chart Properties-Sort Tab-> Check expression -> Descending put below condition

=SortCountry & YourExpression

Re: Multilevel sort on bar chart

zhadrakas — Wed, 04 May 2016 06:34:48 GMT

Hello Mark,

Go to Chart Properties -> Sort Tab

- Make sure the Sort list is right (1st Country, 2nd Value)

- Sort Country A-Z

- Sort Value Numeric Descending

Re: Multilevel sort on bar chart

tamilarasu — Wed, 04 May 2016 06:44:05 GMT

Hi Mark,

Try sort expression as

=Sum(Value)&Ord(Location)

Attached the updated file.

Re: Multilevel sort on bar chart

florentina_doga — Wed, 04 May 2016 06:57:39 GMT

maybe this ! !

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 06:59:04 GMT

This actually achieved the desired sort order by chance. But it is not working well. For one thing, the expression does not consider the Country.

It should sort by Country first

THEN

sort by Sum(Value)

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 07:01:18 GMT

Yes, the straight table you have is correctly sorted into the desired order but how do you translate that same order onto the bar graph?

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 07:06:28 GMT

Tried and yes this looks good! Quick question, is there an alternative way of achieving this from within the bar chart only and without having to use the Load Script?

Re: Multilevel sort on bar chart

florentina_doga — Wed, 04 May 2016 07:12:02 GMT

try this

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 07:20:52 GMT

Good approach and considered this earlier. I failed to mention that I can't change the look and feel of the chart. X-axis should always just be the Location.

Re: Multilevel sort on bar chart

sunny_talwar — Wed, 04 May 2016 08:05:41 GMT

How about this for the sorting expression:

Ord(Country) * 10000 - Sum(Value)

Giving higher weight to Country by multiplying it by 10000 and then subtracting Sum(Value)

The number to be multiplied with will depend on the max value of Sum(Value). You would want the 10,000 in my example to be bigger than the max value of Sum(Value). In our case the max was 1000 so I randomly picked 10000. But if in your actual scenario the max(value) is 600000000, I would multiply by 1000000000.

I guess a better image with slight modification of data

Re: Multilevel sort on bar chart

Kushal_Chawda — Wed, 04 May 2016 08:29:09 GMT

You can try the way suggested by others as alternative. But doing so in script will keep the things well structured rather than going for complex expressions.

Re: Multilevel sort on bar chart

Anonymous — Wed, 04 May 2016 08:31:02 GMT

according to the sort tab we can sort is possible

Re: Multilevel sort on bar chart

tamilarasu — Wed, 04 May 2016 16:09:25 GMT

Yes. I didn't notice it properly. You can use Sunny's solution. But you no need to enter the multiplied value manually. Instead you can use below expression.

Ord(Country) * Max(Total Aggr(Sum(Value),Location)) - Sum(Value)