topic Month Name to Month Number in Qlikview in QlikView

Month Name to Month Number in Qlikview

Tue, 14 Sep 2010 16:28:58 GMT

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

Month Name to Month Number in Qlikview

Miguel_Angel_Baeyens — Tue, 14 Sep 2010 17:06:58 GMT

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

Month Name to Month Number in Qlikview

Tue, 14 Sep 2010 17:16:19 GMT

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

Month Name to Month Number in Qlikview

Tue, 14 Sep 2010 18:34:14 GMT

Hi Kumar,

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

REGARDS,

DUSHYANT

Month Name to Month Number in Qlikview

Tue, 14 Sep 2010 22:27:51 GMT

Hey Dushyant,

Thanks a lot dude...

I am trying to understand it...

Regards

Kumar

Month Name to Month Number in Qlikview

nathanfurby — Tue, 19 Oct 2010 16:36:22 GMT

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

Thanks

Month Name to Month Number in Qlikview

Miguel_Angel_Baeyens — Tue, 19 Oct 2010 19:11:34 GMT

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?

Month Name to Month Number in Qlikview

johnw — Tue, 19 Oct 2010 19:34:39 GMT

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

Month Name to Month Number in Qlikview

Miguel_Angel_Baeyens — Tue, 19 Oct 2010 21:20:00 GMT

Good one John. Agree. Cleaner and easier.

Month Name to Month Number in Qlikview

nathanfurby — Wed, 20 Oct 2010 10:16:05 GMT

Thanks to both.

Re: Month Name to Month Number in Qlikview

Fri, 28 Jun 2013 08:40:40 GMT

The easiest way will be to just num the date.

Dates are duel functions. Thus you will be able to num('May') and get 5

Re: Month Name to Month Number in Qlikview

matthewjbryant — Mon, 07 Jul 2014 13:43:51 GMT

Yep. This's the solution for me!

Re: Month Name to Month Number in Qlikview

Tue, 07 Oct 2014 12:29:22 GMT

Exactly what I was looking for!

Re: Month Name to Month Number in Qlikview

Sat, 18 Oct 2014 11:23:04 GMT

Thanks John ... i was looking for this kind of solution

Re: Month Name to Month Number in Qlikview

Wed, 03 Dec 2014 12:18:19 GMT

This worked perfectly: num(month(SOMEDATE),'00')

Thank you.

Re: Month Name to Month Number in Qlikview

Anonymous — Fri, 06 Feb 2015 13:07:37 GMT

This solution helped me today...many Thanks!

Re: Month Name to Month Number in Qlikview

Thu, 12 Feb 2015 07:18:09 GMT

I was looking for this.... Got it.

Re: Month Name to Month Number in Qlikview

dirk1955 — Tue, 10 Mar 2015 12:04:14 GMT

Hi guys,

Isn't it much simpler to temporary change the variable "MontNames".

I work like this.

My normal monthnames format:

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

Just before the load-script where i need the months shown as numbers i change the var monthnames to:

SET MonthNames='01;02;03;04;05;06;07;08;09;10;11;12';

After the load-script i change it back to its original format.

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

Greets

Dirk

Re: Month Name to Month Number in Qlikview

ankitbisht01 — Mon, 29 Jun 2015 10:13:16 GMT

Thank you very nice solution

Re: Month Name to Month Number in Qlikview

psankepalli — Fri, 01 Apr 2016 15:28:18 GMT

Hi,

Please find the attached QVW, which converts the date to your required format.

First I created variable

vDate =Date#(TextBetween(InputDate, ' ', 'th') & '-' & SubField(InputDate, ' ', 3) & '-' & SubField(InputDate, ' ', 4), 'dd-MMMM-yyyy')

then re-used that variable into text box

=Date(vDate,'DD-MM-YYYY')

hope this will solve your problem

Thanks