Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kwdaniels
New Contributor III

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

11 Replies
Not applicable

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

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
New Contributor III

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

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

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

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

Not applicable

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

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
New Contributor III

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

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.

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

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

];

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

Have you tried creating the quarters as a dual?

Not applicable

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

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

You can as well try what Colin suggests.

mrutyunjaya
New Contributor II

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

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

Community Browser