topic Re: Sorting by Month with 3 characters in QlikView

Sorting by Month with 3 characters

Fri, 18 Oct 2013 15:46:45 GMT

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??

Re: Sorting by Month with 3 characters

jpapador — Fri, 18 Oct 2013 15:48:55 GMT

In the chart properties go to the sort tab. Choose month and sort by the following expression:

Match(Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov' , 'Dec')

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 15:53:47 GMT

Can u attach the example?

Re: Sorting by Month with 3 characters

Anonymous — Fri, 18 Oct 2013 16:00:20 GMT

Thomas,

It's not a good idea to use MonthNames in this way. I suspect that it simply renames Jan to Oct, Feb to Nov, etc..

You rather have to create fiscal year which starts in October and ends in September.

As for the sorting, looks like you're using "descending" (?)

Regards,

Michael

Edit: example how to create month correctly:

dual(month(DateID), if(month(DateID)>=10, month(DateID)-9, month(DateID)+3)) as FiscalMonth

It will have Oct as 1, Sep as 12 when you sort by number

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 16:05:39 GMT

didn't work. . . thanks for the suggestion though.

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 16:07:38 GMT

sorting is tried both ways, no change, though the order is correct, just backwards. . . .

How do I create a fiscal year which starts in October and ends in sep? do tell!

thanks for the suggestion

Re: Sorting by Month with 3 characters

jagan — Fri, 18 Oct 2013 16:09:35 GMT

Hi,

Try using like this

LOAD * INLINE [

Month, SortOrder

Oct,1

Nov,2

Dec,3

Jan,4

Aug,12 ];

Now use SortOrder in Properties -> Sort -> Expression -> =SortOrder select Ascending/Descending as required.

Regards,

Jagan.

Re: Sorting by Month with 3 characters

jagan — Fri, 18 Oct 2013 16:11:46 GMT

Hi,

or try this in chart Properties -> Sort-> Expression

=Match(Month, 'Oct', 'Nov' , 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep')

Hope this helps you.

Regards,

Jagan.

Re: Sorting by Month with 3 characters

Anonymous — Fri, 18 Oct 2013 16:12:24 GMT

Just edited my post with the example for the fiscal months

Jagan's way is usable as well, except you get an additonal filed (wich I prefer to avoid)

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 16:28:31 GMT

all great suggestions, so far no go on any of them. .

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 16:30:49 GMT

@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!

Re: Sorting by Month with 3 characters

Clever_Anjos — Fri, 18 Oct 2013 16:31:11 GMT

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

Re: Sorting by Month with 3 characters

jagan — Fri, 18 Oct 2013 16:33:31 GMT

Hi,

Can you attach some sample file.

Regards,

jagan.

Re: Re: Sorting by Month with 3 characters

Anonymous — Fri, 18 Oct 2013 17:00:09 GMT

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

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 17:22:31 GMT

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. . .

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 17:27:09 GMT

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. .

Re: Sorting by Month with 3 characters

Clever_Anjos — Fri, 18 Oct 2013 17:36:36 GMT

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

That was an example

Re: Sorting by Month with 3 characters

Fri, 18 Oct 2013 18:01:26 GMT

Jagan Mohan

Thank you! I got it to work. . . I was getting confused between function names and filed/column names, and now I am good! I just had to think about it a bit more, and all is fine!

thank you!