Month Name to Month Number in Qlikview

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

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

[#4]

Hi Kumar,

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

REGARDS,

DUSHYANT

[#5]

Hey Dushyant,

Thanks a lot dude...

I am trying to understand it...

Regards

Kumar

[nathanfurby]

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]

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]

Thanks to both.