How to convert Month to number format

[bhavvibudagam]

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.

[swuehl]

Maybe like this used for example in your script:

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

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

[Anil_Babu_Samineni]

May be these?

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

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

[its_anandrjs]

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;

[jagan · Accepted solution]

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.

[bhavvibudagam]

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)

[bhavvibudagam]

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

[jagan]

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.

[its_anandrjs]

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;