topic Re: Sort Month Name in QlikView

Sort Month Name

Thu, 09 Jul 2015 01:03:20 GMT

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

Re: Sort Month Name

Thu, 09 Jul 2015 01:22:18 GMT

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.

Re: Sort Month Name

Thu, 09 Jul 2015 01:28:58 GMT

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

Re: Sort Month Name

Thu, 09 Jul 2015 02:07:37 GMT

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

Re: Sort Month Name

Thu, 09 Jul 2015 02:21:57 GMT

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 ?

Re: Sort Month Name

Thu, 09 Jul 2015 02:25:26 GMT

Hi Steven,

Are there any method without creating any inline table ?

thanks for your comment

Re: Sort Month Name

Thu, 09 Jul 2015 02:27:38 GMT

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

Re: Sort Month Name

jagan — Thu, 09 Jul 2015 02:41:36 GMT

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.

Re: Sort Month Name

johnca — Thu, 09 Jul 2015 02:48:33 GMT

Is this what you're looking for?

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

Re: Sort Month Name

Thu, 09 Jul 2015 02:57:58 GMT

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

Re: Sort Month Name

jagan — Thu, 09 Jul 2015 03:03:04 GMT

Hi Indra,

For this you need to uncheck the following option

Properties -> Sort -> Uncheck State option.

Regards,

Jagan.

Re: Sort Month Name

Thu, 09 Jul 2015 03:06:26 GMT

Hi Jagan,

I have already uncheck the state option. I only check expression option and add the expression.

Still dont get the problem why. really confuse about it.

thanks

Re: Sort Month Name

jagan — Thu, 09 Jul 2015 03:07:15 GMT

Hi Indra,

Can you attach the sample file?

Regards,

Jagan.

Re: Sort Month Name

Thu, 09 Jul 2015 03:15:42 GMT

Hi,

Sorry Jagan, i cant attach any file.

Is there any possibilities that the problem comes from the load order and makes Dec show in the first row ?

When i'm not choose the month it shows correctly,

Jan - Dec

But when i choose one month i mixed up again.

Re: Sort Month Name

settu_periasamy — Thu, 09 Jul 2015 03:25:23 GMT

Hi,

may be like this, in Sort - > Expression (remove all other sort selection )

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

Re: Sort Month Name

jagan — Thu, 09 Jul 2015 03:32:48 GMT

Hi Indra,

Can you attach the screenshot of the Sort tab?

Regards,

jagan

Re: Sort Month Name

Thu, 09 Jul 2015 04:12:09 GMT

Use Only({1}MonthNum) in Sort tab -> Expression

Regards,

KKR

Re: Sort Month Name

Thu, 09 Jul 2015 04:25:06 GMT

Hi Kran,

Wow, super thanks Kran. It solved my problem. But can you explain why i need to use Only{1} like that ? Still dont get the idea.

Thanks

Re: Sort Month Name

jagan — Thu, 09 Jul 2015 04:30:02 GMT

Hi,

If you use this approach then in every Month listbox in the Dashboard you have to use this. This is not a feasible solution.

Regards,

Jagan.

Re: Sort Month Name

Thu, 09 Jul 2015 04:34:19 GMT

Hi Jagan

Do you mean that when i select Jan in first sheet, Month in the other sheet will be Jan also ? Can you give me the sample scenario ?

thanks for your opinion