Converting text like "Apr" to sortable month field

[mhassinger]

I am just not getting this to work right.

I have a field that is not recognized as an actual month by QlikView because it is parsed out of a longer text field. So I have a field called month that works for selecting, but can't sort the right way (Jan, Feb, Mar, etc.).

It seems I can't just use Month() on it. I've also tried various ways to use the Date() function first, but to no avail.

So how do I get this text field that contains values like "Apr" to still display "Apr" to the user, but are recognized as valid months for sorting?

[jvitantonio]

Hi, if you don't have a date field where you can extract the month, try creating an inline table that join to the month field you have:

LOAD dual(Month,MonthNum) as Month INLINE [

    Month, MonthNum

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    Jun, 6

    Jul, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

    ];

The dual function will create a text field with the month value, but can also be treated as a number. This way you'll be able to sort your month.

i hope this helps.

[swuehl · Accepted solution]

Try

Month(Date#('Apr','MMM'))

or replace 'Apr' with your appropriate field name.

edit: Take care that your MonthNames variable holds the month names appropriate to your field values.