Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kwdaniels
Partner - Creator
Partner - Creator

Can we control the sort order of legend items in a chart?

See below a line chart that reports revenue trends by quarter, with each quarter being represented by a different colored line. Unfortunately the legend presents the billing quarters out of natural sequence. Is there a way to force them to sort alphabetically?

LegendSequence.png

12 Replies
Not applicable

Hi,


Turn off auto sorting and custom sorting (deselect all options). This will now sort by load order which you can control in the script. 


Hope this helps. 


Regards,

Pravesh

kwdaniels
Partner - Creator
Partner - Creator
Author

Thanks, Pravesh. I turned off the sorting, but that didn't help. However, I experimented with the sort order, changing from Ascending to Descending, and I discovered that the 2015-Q2 and 2015-Q3 values are ALWAYS at the bottom of the list, so the issue seems to be related to those specific values. See below a screenshot of the legend in descending alphabetical order.

My first thought was there must be a hidden character at the beginning of those two items that make them sort differently from the rest, but that can't be the case, since they always appear at the bottom of the list, whether I choose Ascending or Descending sort order. I'm still puzzled as to what's happening...

LegendSequenceDescending.png

Not applicable

Perhaps it is an issue of formatting. Experiment with wrapping the field in text or text# or any date formats?

Not applicable

Hi Ken,

Try using WildMatch function in Sort by Expression.

Tick the 'Sort by Expression' option in the Sorting tab and write your expression as

=Wildmatch(YearQuarter, '2014-Q1', '2014-Q2', '2014-Q3', ........, '2015-Q3')

Hope this helps.

Regards,

Rohan

kwdaniels
Partner - Creator
Partner - Creator
Author

Thanks for your suggestion, Rohan. I was hopeful it might work, but those two quarters (2015-Q2 and 2015-Q3) continue to appear at the bottom of the list for some reason. We may need to contact Qlik support.

Here's the sorting expression I created:

Wildmatch([Billing Year-Quarter], '2006-Q1','2006-Q2','2006-Q3','2006-Q4','2007-Q1','2007-Q2','2007-Q3','2007-Q4','2008-Q1','2008-Q2','2008-Q3','2008-Q4','2009-Q1','2009-Q2','2009-Q3','2009-Q4','2010-Q1','2010-Q2','2010-Q3','2010-Q4','2011-Q1','2011-Q2','2011-Q3','2011-Q4','2012-Q1','2012-Q2','2012-Q3','2012-Q4','2013-Q1','2013-Q2','2013-Q3','2013-Q4','2014-Q1','2014-Q2','2014-Q3','2014-Q4','2015-Q1','2015-Q2','2015-Q3','2015-Q4','2016-Q1','2016-Q2','2016-Q3','2016-Q4','2017-Q1','2017-Q2','2017-Q3','2017-Q4')

Everything sorts correctly except for those 2 quarters.

Colin-Albert

Define your quarters as a dual field in your load script so they have both a numeric and text value then they will display the text sorted in numeric order.

Below is a test script

Test:

load

         Qtr,

          dual(Qtr, num(replace(Qtr, '-Q',''))) as Quarter

inline

[ Qtr

2014-Q1

2014-Q2

2014-Q3

2014-Q4

2015-Q1

2015-Q2

];

Colin-Albert

Have you tried creating the quarters as a dual?

Not applicable

That's strange. The expression seems to be correct.

You can as well try what Colin suggests.

mrutyunjaya
Contributor III
Contributor III

Hi Ken,

How to automate this function, as I am using similar logic for one of my work.

Ex: Replacing ('2006-Q1','2006-Q2'..............) with date function for all the characters.


Reg,

Mrutyunjaya