Sort Stacked Chart by Y and 2nd Dimension

[patbuchanan]

I've been asked to create a stacked chart to sort by Grand Total and then in each bar to sort the values from High to Low.  When it displays all of the choices it doesn't sort High to Low for each bar but if I click on just one Location then it sorts properly, just not when all data is displayed.  I saw some examples show aggr but when I tried that with my data still no luck...  I've attached a sample file.

Notice Location 7 along with others are not sorted High to Low

But if I click just on Location 7 then it does sort the data properly...

[cjohnson]

Hi Pat,

Please change your sort columns to the following:

Additionally, I noticed that your expression doesn't have any kind of aggregation around it sum(), avg() etc. Please use either the sum or only function to get your KPI's...If you have more than one value per Location GRBS combination Qlikview won't know what to do with it.

See attached for more details.

Thanks,

Camile

[maxgro]

like this? see attachment

[patbuchanan]

I've tried that but still doesn't sort properly as a whole.  Below is an image of your option.  They want me to sort each Location from High to Low by sum of Total field.

[Anonymous]

use the following sort Option

but the last two Locations are not quite correct

by the way: as you see there is a red line after max(Total)

I renamed Total to Total1 in your script and the red line was gone

Total is a reserved word, so try to avoid These reserved words

[patbuchanan]

This is pretty close but the legend on each bar uses different colors and list the items in the legend many times with the different colors.  Looks like I need to use this along with some color formatting.

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

hope this helps

regards

Marco

[patbuchanan]

Thanks a lot this appears to do the trick.

[#9]

The palette of colors is limited, in this case if we have more than 50 GRBS ! the colors is repeated so there is a way to stretch so as to have a plenty of colors?

Your help is very appreciated!