Matching sort order of legend and graph - dual function not working

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

[vinieme12]

Dual(text, number)

dual will store a TEXT REPRESENTATION of a NUMBER

So by using DUAL() you are really assigning the number 1 a TEXT representation of "Strongly agree"

and not assigning "Strongly agree" a numeric value of 1,  there's a difference

Also as you can see from the syntax and definition  trying to do Dual( text, text) is never going to work

Best way to assign sort orders is to create separate small tables and link to the concerned dimension

ResponseSortOrder:

Load * Inline[

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

];

AgeGroupSortOrder:

Load * Inline[

AgeGroup,ag_SortOrder

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

];

When you export a chart that have a DUAL() field, it will export the Numeric value  and not the text representation

hope this was clear enough

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/Formatting...

[rds694]

I have tried creating a key table and linking to the dimension (I've combined all dimensions in one as a crosstable). However, the issue with that is that the sort order for the bars does not get reflected in the sort order for the legend (you can see that in the first picture in my original post). How do I get the sort order to match between the bars on the chart and the legend?

[rds694]

I wanted to follow up on this - any thoughts on what I can do?

[rds694]

Hi, I still haven't been able to figure this out. It seems like you're suggesting using tables for sort order, which I have done, but that sort order does not apply to the chart legend. Any idea what I can do?