Sort Month Name

[#1]

Hi,

I have a question. I have load a calendar data successfully. When I want to show Month Field using list box, the first data is Dec not Jan. I have two field to be used in sort column, MonthNum (1-12) and MonthName (Jan-Dec). Anybody knows the method to sort month name correctly ?

Really appreciate your help, thanks

[#2]

Hi,

Do you have month number like 1-12.. If you You may sort it by Month number column.

To do this-->Right Click on List Box-->Properties-->Sort Tab-->Expression and then Select the field which you want to use in Sorting. It may be different from the list box values.

For Example:

Display Month Name and sort by Month Number.

[#3]

If you have created Month field in QlikView using Month() function, then QV stores both numeric and string representation of it (Dual). And it should sort the values from Jan to Dec by default. If not, check sort tab. If the field is not created using Month() function, then you have to use dual() function to assign both number and text values to it.

Please share your calendar script/qvw. We shall resolve the issue for you.

Regards,

KKR

[#4]

Hi Indra

There are a few possible causes of your month sort starting at a month other than January.  Firstly do you have any data from the previous December in your data set as if you sort by date order then December would show up first.

If you have :

Try this as the sort option for you list box

You can also set a sort reference in script using e.g.:

CAL1:
Load *inline [
CAL_Month,SortRef
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11
12,12
];

This version will allow you to sort your months where January would be the first month.  You can use the same technique to sort by other orders e.g. with April as the first month in the sort order.  This would be achieved from the version of the script below:

CAL2:
Load *inline [
CAL_Month,SortRef1
4,1
5,2
6,3
7,4
8,5
9,6
10,7
11,8
12,9
1,10
2,11
3,12
];

To make use of this all you need to do is to select the expression option in the sort tab and call the sort field e.g

For starting with January

which will give

or for starting in April

which will give

Hope that helps.

Kind regards

Steve

[#5]

Hi,

Thanks guys for your comments,

my data structure is like this :

Year     MonthNum          MonthName          Date

2007          12                         Dec               2007/12/31

2008          1                           Jan                2008/1/1

.....          ......                         .....                    .....

I write in sort expresstion (MonthNum&MonthName).

Thanks Steven, Your idea is really good, but are there another method without creating any inline table ?

[#6]

Hi Steven,

Are there any method without creating any inline table ?

thanks for your comment

[#7]

Hi Kran,

My data structure is like this

Year     MonthNum          MonthName          Date

2007          12                         Dec               2007/12/31

2008          1                           Jan                2008/1/1

.....          ......                         .....                    .....

I write in sort tab expression = MonthNum&MonthName

thanks

[jagan]

Hi Indra,

How did you arrived month field?  If you arrive Month field using Month() then go to Sort tab and select Number option this will work.

Always derive the date related fields using the Date functions, so that you won't get this type of sorting issues.

LOAD

*,

Month(Date) AS Month,

Year(Date) AS Year

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[johnca]

Is this what you're looking for?

month(date#(monthname,'MMM')) as month_num

[#10]

Hi Jagan,

I have change the expression in sort tab to MonthNum (Without MonthName) and it works perfectly. But when i choose onr month from the list box the order change and become disorder again. Do you know the problem ?

thanks