Can't get legend sorting to match graph sorting

[rds694]

Hi, I'm having issues trying to get the sort order of the legend of my chart to match the sort order of the bars. As can be seen, it's currently not working.

I tried using a dual function with a response sorting key in order to solve the issue, but it's not working. I'm trying to use a dual function to combine the index into the field, but I'm having trouble getting it to work.

Here's a portion of my sorting key:

[SortingKey]:

Mapping Load Response, SortOrder
Inline
[
Response, SortOrder

'Strongly agree', 1
'Agree', 2
'Neither agree nor disagree', 3
'Disagree', 4
'Strongly disagree', 5

.....

'18-25', 1
'26-35', 2
'36-45', '3'
'46-55', 4
'56-65', 5
'65+', 6

.....

]

And then here's me trying to use the dual function, but as can be seen, it's not really working. For some values it seems like it's assigning random values from elsewhere in the table. And it sometimes behaves differently when using a number vs. a string as the alternative (for values not explicitly listed in my key.

[Group2Final]:
Load
Group2Response as Group2ResponseFinal
,ApplyMap('SortingKey', Group2Response, -99) as NumberSorting
,Dual(Group2Response, ApplyMap('SortingKey', Group2Response, -99)) as NumberCombined
,ApplyMap('SortingKey', Group2Response, 'abcde') as StringSorting
,Dual(Group2Response, ApplyMap('SortingKey', Group2Response, 'abcde')) as StringCombined
Resident Group2;

Any thoughts on why this could be? I can't figure out what I'm doing wrong with the dual function.

[stevedark]

Hi @rds694 

The good news is that your ApplyMap is easy to fix, I think. Just remove the single quotes around the strings. An inline load effectively treats that chunk of the load script as a CSV, and you wouldn't expect single quotes in a CSV.

The bad news is that it may not cause the sort order of the legend to match the bars anyway. If you put the legend down the right hand side it will be less obvious, perhaps?

The legend in your screenshot is adding very little to the chart here, as the labels are on the bars, I understand that may not always be the case though.

I would be very wary of having the same number on multiple dual values, as when the lookup works Qlik will see Strongly Agree and 18-25 as the same thing - as it looks at dual fields by the number only when looking for uniques (this can really mess with your head if it happens and you don't know why!). Perhaps number the first set at 101 - 105 then the next at 201 - 206 etc.

Legends can be a pain, and I have some apps where I have created the legend separately as an image and then placed it onto the sheet that way.

Hope that helps.

Steve

[rds694]

Hi,

This chart is just an example - I can't show the actual data in my charts. So having the legend match up will be more important.

I don't see why the single quotes should be an issue - I can try taking them out, but I have used single quotes in other keys in the same application. And looking at the example table I'm making, it seems the ApplyMap is working correctly. If you look at the "NumberSorting" and "StringSorting" columns, you'll see it's working as intended. The issue is in using the dual function to assign and index value to a text field. I don't know if the duplicate values is the issue, but I can give it a shot to make sure every value is unique.

But even if I do all of this, will it not actually make the legend match the graph? Because that's the entire goal of what I'm doing.

Thanks for your help!!!

Ryan

[stevedark]

Hi @rds694 

Looking back at the screenshots you shared I can see that the applymap is working, but the issue I mentioned that occurs where you have duals with two different text values sharing the same number (e.g. where you have 16-25 and Female on the same row of the table).

The dual function always requires a string as the first parameter and number as the second. The string combined field is therefore flawed (if no applymap value is found). Stick with number combined.

You should be able to sort this numerically, and this will order the bars, but sadly having a horizontal legend it may well not reflect the order of the bars.

Steve