Month- Number

[#1]

Hi All,

How can i call a number based on the month i.e. if month is January ,number should be 1, similarly if the month is Feburary ,number should be 2 and so on.

Thanks

Anurag Gupta

[phaneendra_kunc]

Assuming you have month field in your data model.

easiest way is to add a inline table and do a left join to your calendar.  or try using "Master Calendar script" which you can find it in community.

Month_Sort:

LOAD * INLINE [

   Month, MonthSort

    January, 1

    February, 2

    March, 3

    April, 4

    May, 5

    June, 6

    July, 7

    August, 8

    September, 9

    October, 10

    November, 11

    December, 12

];

[Anonymous · Accepted solution]

Hi Anurag,

Here is a sample master calendar script if you would like to incorporate this in your application:

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Num(MakeDate(2001,12,31));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS [Calendar Date],

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS Month,

num(Month(TempDate)) AS MonthNum,

Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate)))  as CalendarYYYYMM,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS Year,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

Date(MonthStart(TempDate)) as CalendarMonthStart,

Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[#4]

Try this:    date(num(Ex_date),'DD/MM/YYYY')

[jagan]

Hi Anurag,

Try like this

LOAD

*,

Match(MonthName, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')

FROM Data;

Note: Replace MonthName with your actual field name.

Regards,

Jagan.

[shantanu73]

Hi Jaghan,

I hope all the functionalty of sense will worj in qlikview I am right?Thanks in advance

Shantanu

Sent from RediffmailNG on Android

From: &quot;Jagan Mohan&quot;qcwebmaster@qlikview.com

Sent:Fri, 22 Aug 2014 09:40:55 +0530

To: Shantanu Sardar shantanusardar@rediffmail.com

Subject: Re: - Month- Number

Qlik Community Month- Number reply from Jagan Mohan in New to Qlik Sense - View the full discussion Hi Anurag, Try like this LOAD*,Match(MonthName, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')FROM Data; Note: Replace MonthName with your actual field name. Regards,Jagan. Reply to this message by replying to this email, or go to the message on Qlik Community Start a new discussion in New to Qlik Sense by email or at Qlik Community Following Jagan Mohan in these streams: Email Watches © 1993-2014 QlikTech International AB Copyright &amp

; Trademarks | Privacy | Terms of Use | Software EULA

[andymanu]

Hi Sean,

Thanks for your helpful code.

I need a small clarification,

I added the below command to the code since I want to get the Year ans Month Data field as below,

Year(TempDate) & '/' & '' & Month(TempDate) AS CalendarYearAndMonth,

When I generate a line graph for actual sales which got data from July 2017 to July 2018, it h=gave me the below output. Ideally, the 2018/July data value should be at the end of the chart after 2018/June.

Could you pls advise me, how to accomplish the said issue?

Thanks.

Andy