topic Re: Line chart sorting for legend dimension in App Development

Line chart sorting for legend dimension

Or — Mon, 05 Apr 2021 12:47:20 GMT

The issue I am facing is trying to sort the legend dimension in a line chart alphabetically without upsetting the X-axis sorting, in a context where some legend dimensions start later on the X axis than others.

Sample data:

Load * INLINE [
Currency, Month, Value, Filter
EUR, 1, 100, A
EUR, 2, 300, A
EUR, 3, 400, A
USD, 1, 200, A
USD, 3, 500, B
GBP, 2, 250, B
GBP, 3, 600, A
];

My line chart is composed of Month as the X-axis dimension, Currency as the legend dimension, and the measure expression:

Sum({< Filter = {"A"} >} Value)

When sorting by Month and then by Currency (alphabetically), instead of getting EUR-GBP-USD, I get this chart with USD ahead of GBP:

The reason being, presumably, that GBP has no values for Months 1 or 2 whereas USD and EUR do, so they are getting sorted ahead of GBP.

I'm looking for any sort of workaround or method where the values would get sorted correctly - first by month (so the X-axis remains correct) and then by currency (alphabetically). This is a simplistic example - the actual formula used has multiple aggregations and more complex set analysis.

Solutions already attempted without success:

Move the currency to first on the sort list (breaks the sorting by months on the X axis)

Sorting the second dimension by e.g. Only({1} Currency)

Sorting by load order

Would appreciate any insights or ideas... thanks in advance!

[Edit]

Attached is the app used to generate this sample

Re: Line chart sorting for legend dimension

tresB — Mon, 05 Apr 2021 12:29:49 GMT

Try creating a master dimension with dual(), something like:
=Dual(Currency, Match(Currency, 'EUR', 'GBP', 'USD'))

Then use this dimension for chart coloring. You might want to set your colors in master dimension if anything specific.

Credit: Patrik Lundblad

Re: Line chart sorting for legend dimension

Or — Mon, 05 Apr 2021 12:48:00 GMT

@tresB Unfortunately, this doesn't help at all. This issue here isn't to set a specific order for currencies - I'm looking for a simple alphabetical sort - but to have Qlik sort them in a consistent manner that doesn't change based on the values of the actual measure.

Near as I can tell, the issue appears to be that Qlik is sorting first based on month, and since GBP has no value for Month = 1, it's getting sorted behind the other currencies. Changing the currency dimension itself doesn't seem to modify this behavior in any way, including adding dual().

Re: Line chart sorting for legend dimension

tresB — Mon, 05 Apr 2021 12:53:47 GMT

I didn't go much deep into it. I saw :

Re: Line chart sorting for legend dimension

Or — Mon, 05 Apr 2021 13:23:54 GMT

@tresB I'm not sure how you arrived at that chart - using the exact formula you posted, I am not getting any change in the sort order on my end... it's still putting USD ahead of GBP. I've tried doing this both at the script level and as a master dimension and neither has resulted in any change to the sort order.

Since your object looks different than mine, perhaps this is a cross-version issue? I'm looking at this on Qlik Sense April 2020 SR11 here.

Re: Line chart sorting for legend dimension

tresB — Mon, 05 Apr 2021 13:48:09 GMT

It seems that you are using your master dimension as second dimension. Instead, use your actual Currency as second dimension and then drag your master dimension (CurrencyDual) for just coloring it (Color By :). That should work.

Re: Line chart sorting for legend dimension

Or — Mon, 05 Apr 2021 14:08:23 GMT

Oops, missed that part of your response - I think it's so counter-intuitive that the sort order of a dimension would be determined by the colors option being set to a dimension that's numeric that I just didn't pick up on it.

Sadly, I'm working with Vizlib Line Chart rather than the native object, and this option doesn't seem to exist in the Vizlib extension, and it also breaks down in the more complex scenario used in my formulas and set analysis (where the values displayed are excluded by the selection and then made available through set analysis) - all the lines are showing up gray. Back to the drawing board for me... but at least now I know how to get it done with the native version and simple case. Thanks!

Re: Line chart sorting for legend dimension

Or — Mon, 05 Apr 2021 14:17:34 GMT

Just in case anyone comes across this again, for further information, here's what happens when a month is selected and the expression is modified with a set analysis of:

Month =

When the coloring is removed, it goes back to showing the list of currencies, but again, the order is not correct:

So this seems to be a case where the workaround applies only to a relatively simple use case but not more complex ones. Still, it is a good workaround when appropriate!