In a report, I have added a listbox displaying the monthname (three letters; Field calculated in the script based on a date-time stamp each record carries). When I want to sort these, I have the option to either sort them alphabetically (putting April first) or by numeric value (with the year (default), that makes the order dependent on the data available for different years; Alternatively, if I split off just the month_name, this does not do anything).
Both of these options result in a sorting I do not want for months.
It should be possible to sort the months by their calendaric order, that is just too basic to assume it might not be possible. Can anybody tell me how?
If you use any of the QV date/time functions that return a dual (i.e. creating a Month name as text representation and month start date as numerical representation), sorting by number should just work fine:
Monthname(DATEFIELD) as YearMonth,
or
Date(monthstart(DATEFIELD),'YYYY-MM') as Month,
or maybe use
Month(DATEFIELD) as Month
which use 1-12 as numeric representation.
How have you created your field and what is your expected outcome?
I am using the Monthname function already - but I have split off just the Month_text by using the LEFT function since I want to avoid any potential for confusion as much as I can - some people will always be intelligent enough to get something wrong, I'm just trying to make their lifes harder 😉
I have changed this back for now and removed the LEFT function - now it reads "May 2003" or so and sorting by numeric value is no problem.
There is another issue now: The month listbox now displays the year, so I don't need the year_listbox anymore for that, but I still need it when the user selects a calendar_week. I tried to fix this by simply concatenating the weeknr. and year, but it doesn't work that way, both are numeric_integer values. It seems QlikView does not know CONVERT or CAST and I couldn't find any of the functions I remember to convert to a string.
