Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting by Month with 3 characters

Fiscal Year is from October through September.

I rearranged the order of the month names with

MonthNames='Oct;Nov;Dec;Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep';

However, I can't get them ordered on the X axis properly.  The order is in reverse.

I tried the Dort Dimensions but could not get the sort order to change. . .

Any suggestions??

17 Replies
Not applicable
Author

@Jagan Mohan

This suggestion appears to be the best implementation workaround.  however, I can't get this to override the monthly sort, even when unchecked for sorting. . . not sure why. . .

The system data is stored YYYYMM, and that works, just doesn't present well, but maybe then I do the inline load of 201301, Oct

201302, Nov

let me give that a try. . . thanks!

Clever_Anjos
Employee
Employee

Try something like this

load

  *,

  dual(

  date(Data,'MMM'),

  AddMonths(Data,-3)  // Trick...

  ) as FiscalMonth;

load

date(addmonths(MonthStart(Today()),-RecNo())) as Data

autogenerate 24; // generating a small calendar

Capturar.PNG.png

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach some sample file.

Regards,

jagan.

Anonymous
Not applicable
Author

This is because you never tried my suggestion

First, you have to have a calendar.  It is needed for many purposes.

Second, if you use fiscal year, make sure it is reflected in the calendar.

Third - see attached example.  Notice that the FiscalMonthName list box is sorted as you want it.  I use a variable FisclMoShift so most fiscal year rules are satisfied by simply entering the shift and reloading.

Regards,

Michael

Not applicable
Author

Michael,

The Gregorian calendar does not correlate 100% with the fiscal calendar by day.  There are January and March days in Fiscal February, September days in fiscal october, etc,  . . .  so that's why I can't do a direct date conversion. . . The data is already converted to the fiscal month and the individual day is not brought in. . .

sorry to have ignored you for so long. . . 

Not applicable
Author

cleveranjos WOW! more programming than I expected, though I am greatly appreciative.  though it will take me awhile to understand what you wrote and how to use it. . . only worked with this software for about a week on my own. .

t.

Clever_Anjos
Employee
Employee

If you already have a calendar you only need the "dual" function.

That was an example

Not applicable
Author