topic Sort Stacked Chart by Y and 2nd Dimension in QlikView

Sort Stacked Chart by Y and 2nd Dimension

patbuchanan — Tue, 22 Sep 2015 14:20:18 GMT

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

Re: Sort Stacked Chart by Y and 2nd Dimension

cjohnson — Tue, 22 Sep 2015 19:55:07 GMT

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

Re: Sort Stacked Chart by Y and 2nd Dimension

maxgro — Tue, 22 Sep 2015 20:19:01 GMT

like this? see attachment

Re: Sort Stacked Chart by Y and 2nd Dimension

patbuchanan — Tue, 22 Sep 2015 20:20:59 GMT

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.

Re: Sort Stacked Chart by Y and 2nd Dimension

Anonymous — Tue, 22 Sep 2015 20:24:01 GMT

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

Re: Sort Stacked Chart by Y and 2nd Dimension

patbuchanan — Tue, 22 Sep 2015 20:24:04 GMT

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.

Re: Sort Stacked Chart by Y and 2nd Dimension

MarcoWedel — Tue, 22 Sep 2015 22:13:14 GMT

Hi,

maybe one solution could be:

hope this helps

regards

Marco

Re: Sort Stacked Chart by Y and 2nd Dimension

patbuchanan — Wed, 23 Sep 2015 02:54:48 GMT

Thanks a lot this appears to do the trick.

Re: Sort Stacked Chart by Y and 2nd Dimension

Tue, 23 Feb 2016 15:27:47 GMT

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!