Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted

Month Name to Month Number in Qlikview

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
Highlighted

Month Name to Month Number in Qlikview

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

Highlighted
Not applicable

Month Name to Month Number in Qlikview

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

Highlighted
Not applicable

Month Name to Month Number in Qlikview

Hi Kumar,

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

REGARDS,

DUSHYANT

Highlighted
Not applicable

Month Name to Month Number in Qlikview

Hey Dushyant,

Thanks a lot dude...

I am trying to understand it...

Regards

Kumar

Highlighted
Valued Contributor

Month Name to Month Number in Qlikview

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

Thanks

Highlighted

Month Name to Month Number in Qlikview

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?

Highlighted
MVP
MVP

Month Name to Month Number in Qlikview

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

Highlighted

Month Name to Month Number in Qlikview

Good one John. Agree. Cleaner and easier.

Highlighted
Valued Contributor

Month Name to Month Number in Qlikview

Thanks to both.