How can I convert numbers to months (i.e. 1 = January)

[#1]

Hello,

New to Qlikview... please can someone tell me how I can convert numbers to months to that 1 is displayed as Jan, 2 as Feb etc. I have a listbox with the numbers 1 - 12 in - so how can I convert the numbers to text and still retain the functionality?

Thanks,

markp3rry.

[erichshiino]

Hi,

There are some alternatives here:

http://community.qlik.com/message/1895#1895

Regards,

Erich

[jykang0638]

You can write a expression to convert number to months in the listbox properties window.
Open listbox properties window and then choose <expression> at the Field in the General tab.
Write expression like below at the Edit Expression window.
=Pick(monthnum, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
Here, monthnum is the your fileld name.

I hope it will help you.
Steve Kang.

[jagan · Accepted solution]

Hi,

Create a Mapping table as below

MonthMapping:

LOAD * INLINE [

MonthNo, Month

1, Jan

2, Feb

3, Mar

4, Apr

'

'

'

'

];

Now use Month field wherever you need.

Note: Your Month number Field name and above month number field should be same.  In the above script I used MonthNo.  So you replace it with your month field name, so that the two tables will join.

Regards,

Jagan.

[#5]

Thanks - I tried Steve and Jagan's methods. Both worked, but I was unable to sort Steve's solution in date order (i.e. Jan to Dec). Jagan's answer sorted perfectly on Load Order.

[#6]

Hi

you can Create a bucket in the Script(Back/Front).for converting no into string.functionality will remaining as it is   

=if(Month=1,'jan',

        if(Month=2,'Feb',

              if(Month=3,'March',

                    if(Month=4,'April',

                          if(Month=5,'May',

                                if(Month=6,'June',

                                           if(Month=7,'July',

                                                           if(Month=8,'August',

                                                                 if(Month=9,'Septem',

                                                                      if(Month=10,'OCT',

                                                                            if(Month=11,'Nov',

                                                                                 if(Month=12,'DEC'))))))))))))

[hic]

All the above will work. But I would generate it on the fly using

Month(MakeDate(2012,MonthNo))

/HIC

[IAMDV]

Thanks Henric. This is very useful.

Cheers,

DV

[martynlloyd]

Thanks Henric, very elegant, just what I need.

Marty.

[rcorcoran]

Hi Jagan

I have tried your solution and it works but it is bringing back all the Months whether data is loaded for a month or not.  I only have  2014 and only have Jan data but all months are showing is there a way around this?  If I have the numbers it will only show 1 even though there is nothing selected

Thanks

Rhona