Sorting - month name

[qlikeers]

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?

April

May

June

July

August

September

October

November

December

January

February

March

In April:

May

June

July

August

September

October

November

December

January

February

March

April

etc.

[Or]

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]

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]

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]

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.

[qlikeers]

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]

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]

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]

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)

[qlikeers]

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

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