- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps it is an issue of formatting. Experiment with wrapping the field in text or text# or any date formats?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you tried creating the quarters as a dual?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's strange. The expression seems to be correct.
You can as well try what Colin suggests.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »