Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Month Name to Month Number in Qlikview

Hi...

I have the month in the format "Mon" ie August is represented as "Aug". I need to convert the month name to Month Number. The Month function requires a date stamp and I am not able to use the makedate function as it requires month as integer. Can someone help me with getting the month number based on month date?

Thank you.

Regards

Kumar

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hello Kumar,

QlikView adds by default some environment variables when you create a new document, in your Main script tab. Check that MonthNames exists and has correct values.

If so

=Match('Feb', $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))


should return 2. It is likely to be a more efficient way to do that.

Regards

View solution in original post

20 Replies
Miguel_Angel_Baeyens

Hello Kumar,

QlikView adds by default some environment variables when you create a new document, in your Main script tab. Check that MonthNames exists and has correct values.

If so

=Match('Feb', $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))


should return 2. It is likely to be a more efficient way to do that.

Regards

Not applicable
Author

Hey Miguel,

Thanks for coming to my rescue again. The solution worked.

Apart from the topic, can you help me with year to month aggregation? Actually, I have monthly data but not able to find the expression to get the cumulative data from start of year to the current month selected.

Any help would be really appreciated.

Thank you once again.

Regards

Kumar

Not applicable
Author

Hi Kumar,

PFA for your refrence to calculate YTD.....

REGARDS,

DUSHYANT

Not applicable
Author

Hey Dushyant,

Thanks a lot dude...

I am trying to understand it...

Regards

Kumar

nathanfurby
Specialist
Specialist

Hey Miguel - do you know how to get that line working in the load script?

Thanks

Miguel_Angel_Baeyens

Hello Nathan,

Try something like

Match(Date(DateField, 'MMM'), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))) AS Field


Is that what you mean?

johnw
Champion III
Champion III

Looks like I'm late with my answer, but how about this?

month(date#(MonthName,'MMM')) as Month

It's actually building a month field, which has both numeric and text values, with text as the default display. If you really want it to be a number and nothing but, then:

num(month(date#(MonthName,'MMM'))) as MonthNumber

Miguel_Angel_Baeyens

Good one John. Agree. Cleaner and easier.

nathanfurby
Specialist
Specialist

Thanks to both.