topic Re: How to convert Month to number format in QlikView

How to convert Month to number format

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I have a Month field like below

I need to create Month and NumMonth fields separately from above field Month like one field Month contains only jan,feb,mar and nummonth field contains 1,2,3...12.

Please help me how to do.

Re: How to convert Month to number format

swuehl — Mon, 11 Sep 2017 09:14:43 GMT

Maybe like this used for example in your script:

Month( Date#(YOURFIELD,'MMM-YY')) as Month,

Num(Month( Date#(YOURFIELD,'MMM-YY'))) as NumMonth,

Re: How to convert Month to number format

Anil_Babu_Samineni — Mon, 11 Sep 2017 09:16:09 GMT

May be these?

Month( Date#(Month,'MMM-YY')) as Month,

Num(Month( Date#(Month,'MMM-YY'))) as NumMonth,

Re: How to convert Month to number format

its_anandrjs — Mon, 11 Sep 2017 09:30:57 GMT

Try to load different fields this way and i ask to you do you have date field in the data model

Load

Month ( Month ) as MonthField,

NUM ( Month ) as NumMonth

From Source;

Re: How to convert Month to number format

jagan — Mon, 11 Sep 2017 09:31:56 GMT

Hi Bhavani,

Try like this

Derive a data field in script like below

LOAD

Date(Date#(YOURFIELD,'MMM-YY')) AS Date,

Month( Date#(YOURFIELD,'MMM-YY')) as Month,

Num(Month( Date#(YOURFIELD,'MMM-YY'))) as NumMonth

FROM DataSource;

Now use below expression to restrict last 6 months

=Sum({<Area = {B},Month=, NumMonth=, Date={'>=$(=MonthStart(Max(Date), -6))<=$(=Max(Date))'}>}Staff)

Hope this helps you.

Regards,

jagan.

Re: How to convert Month to number format

bhavvibudagam — Mon, 11 Sep 2017 09:38:11 GMT

Hi Jagan,

Thanks for your reply. Actually I don't have the Data field only have Month -Year field as like original post image.

I have tried like below

Month( Date#(YOURFIELD,'MMM-YY')) as Month,

Num(Month( Date#(YOURFIELD,'MMM-YY'))) as NumMonth

by this i got crt month and nummonth fields. But when i take like this

=Max(Month) &

' DUI AND CITATIONS'

its showing like--------> 12 DUI AND CITATIONS

NOT LIKE ---------------> Dec DUI AND CITATIONS (Expecting this)

Re: How to convert Month to number format

bhavvibudagam — Mon, 11 Sep 2017 09:38:55 GMT

No Anand I don't have the date field. I have direct Month - Year field only

Re: How to convert Month to number format

jagan — Mon, 11 Sep 2017 09:41:59 GMT

Hi,

YOu can derive Date field from the month field. It is easier if you use date field for this type of calculations.

If you use Max(Month) you will get numeric one, you can try Text(Max(Month)) or Month(Max(Month))..

Regards,

jagna.

Re: How to convert Month to number format

its_anandrjs — Mon, 11 Sep 2017 09:43:02 GMT

Then try this to convert it into Num date and then proper Month and NumMonth fields.

Load

Month (Date#( Month,'MMM-YY' ) ) as MonthField,

NUM (Month( Date#( Month,'MMM-YY' ) ) ) as NumMonth

From Source;