Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikeers
Creator II
Creator II

Sorting - month name

Hi!

I have a bar chart with the names of the month on the x-axis.

What is the easiest way to sort them (names) so that the current month is at the end of the chart on the right and the rest in succession?

AprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarch

In April:

MayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchApril

etc.

 

Labels (3)
1 Solution

Accepted Solutions
qlikeers
Creator II
Creator II
Author

=IF(num("MonthName")-month(Today())>0,num("MonthName")-month(Today()),12+num("MonthName")-month(Today()))

View solution in original post

12 Replies
Or
MVP
MVP

Assuming your months are duals with the actual date attached, you could use mod(Month(Month),Month(Today())) and sort ascending. If your months are text and you can't use duals, probably your best bet is to pre-load the months in script in the order you want them and then use  load-order sorting.

qlikeers
Creator II
Creator II
Author

Thanks for the solution option. I do not fully understand the first one. The second is obviously possible and efficient, but I don't have a quick way to fix it to make it automatic.

Or
MVP
MVP

The first option is to use actual dates, e.g. Monthname(DateField). This will allow month-based sorting.

The second option essentially requires you read the month names in the order you want them displayed, *before* you read them from your actual data e.g. by using a loop or autogenerate; alternatively, you can read the data pre-sorted by month. Either way works. You can then use load-order sorting (disable all of the other sorting options and Qlik will default to load-order).

jlongoria
Creator
Creator

If you have a field that is considered a date (ex: myDate) then you can use Month() or MonthName() to get a text representation of the month. Both Month() and MonthName() will return a value that is visually represented as text value for a month but which has an internal numerical value (dual). Month() has text representation of Jan/Feb/Mar/etc with dual value of 1/2/3/etc and MonthName() has text representation of 'MMM YYYY' with dual value for the first day of the month. This means that the field values can/will be sorted by their numeric value instead of alphabetically. Example below/attached.

 

jlongoria_1-1615813441685.png

 

 

qlikeers
Creator II
Creator II
Author

Of course, I understand it all. I'm just looking for a way for Qlik to automatically sort the month names in the order I gave in the first post. So when we have March now, March will be the "last" on the chart, it will be followed by February, then January, etc ...

Or
MVP
MVP

How would Qlik be able to 'automate' the sorting of an arbitrary collection of text values? Unless you tell it, it doesn't know that "March" is the third month of the year. It's just a string of text, not a time-series value. If you want some sort of automatic sorting, you need to assign numeric or date values to your text, either by using dates, adding a MonthNum field, or by applying Dual() to your months that would contain the month name alongside its number. In all of these cases, you can then sort as I suggested above using the mod() function. Otherwise, I think you're stuck using something clunky like a Match() function to return the month number and then apply the mod() to that - Match(MonthName,'January','February',...,'December') would return the month number in this context.

qlikeers
Creator II
Creator II
Author

We got it wrong. My month name field is a date which only shows the values as a string. As @jlongoria wrote.
The mod () method doesn't work for me, I don't understand it. So maybe I'm doing something wrong.

Or
MVP
MVP

Sorry, I was rushed and wrote the wrong set of numeric functions there... should be, I think:

Mod(Month(YourField)-Month(Today()),12)

e.g. for March, this will be Mod(3 - 3 ,12) = 0

For April, Mod (4 - 3, 12) = 1

etc.

[Edit] Tested this locally:

=Mod(Only(Month([MyDateField])-Month(Today())),12)

Or_0-1615819588761.png

 

qlikeers
Creator II
Creator II
Author

I need this order (according to the first post):

April | May | June | July | August | September | October | November | December | January | February | March