Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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 ?
Hi Steven,
Are there any method without creating any inline table ?
thanks for your comment
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
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.
Is this what you're looking for?
month(date#(monthname,'MMM')) as month_num
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