6 Replies Latest reply: May 18, 2016 10:30 PM by Purushotham M

# Month Number to Month Name conversion

Hi,

I have a Month field in my Table. And that Field contains values Like this = 'JANUARY', 'FEBRUARY' Like this format. In some of my calculation I am using Maximum Month. So I create a variable vMaxMonth = max(Month).

To Make the Month as Number values I used this piece of code in the script:

if(capitalize(MONTH) = 'January', 1,

if(capitalize(MONTH) = 'February', 2,

if(capitalize(MONTH) = 'March', 3,

if(capitalize(MONTH) = 'April', 4,

if(capitalize(MONTH) = 'May', 5,

if(capitalize(MONTH) = 'June', 6,

if(capitalize(MONTH) = 'July', 7,

if(capitalize(MONTH) = 'August', 8,

if(capitalize(MONTH) = 'September', 9,

if(capitalize(MONTH) = 'October', 10,

if(capitalize(MONTH) = 'November', 11, 12))))))))))) as [Month],

But when I am showing month in the list box I have to show the Month Like this: Jan, Feb, Mar, Apr Like this.

I was also trying to use the INLINE to Make the Month ordered like this:

Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

];

But since the Month field is getting number value with the using of IF statement, INLINE month can not match with that.

Can anybody guide me how can i achieve above mentioned functionality from the script?

Thanks,

Sk

• ###### Re: Month Number to Month Name conversion

This will return the month in Dual format so in text format it will display the Short Month Name and in numeric format can be sorted.

=Date(MakeDate(2001, Month(Today())),'MMM')

You can substitute Today() with your actual date field.

• ###### Re: Month Number to Month Name conversion

You can try this:

Month(MakeDate(Year, Date#(Capitalize(MONTH), 'MMMM'))) as Month

• ###### Re: Month Number to Month Name conversion

Or maybe just

Month( Date#( Capitalize(MONTH),'MMMM')) as Month

• ###### Re: Month Number to Month Name conversion

Hi All,

I got a solution of this problem by Gysbert Wassenar in another forum in Qlik community. But thank you all for providing a solution for my problem.

Thanks a lot!

Cheers,

Sk

• ###### Re: Month Number to Month Name conversion

Awesome .

Best,

Sunny

• ###### Re: Month Number to Month Name conversion

Hi,

Here is the work around for Month_Name.

Master_Calendar:

Week(Date_Tmp) as Week,

Year(Date_Tmp) as Year,

Capitalize(Month(Date_Tmp)) as Month,

Day(Date_Tmp) as Day,

WeekDay(Date_Tmp) as DayWeek

RESIDENT TmpCalendar;

INNER JOIN

Month_Tmp: //to ensure a correct "sort by load order"

Month, Month_Name

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,Jun

7,Jul

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

DROP Table TmpCalendar;