How to create a "Week in Month " field with the help of temp_Date

[madhubabum]

Hi all

I have created the temp_Calendar as following :

LET vMinDate = NUM(Date#('2014-01-01', 'YYYY-MM-DD'));    //FY13 and onward

LET vMaxDate = NUM(Date#('2016-12-31', 'YYYY-MM-DD'));   

temp_CALENDAR:

LOAD

    Date($(vMinDate) + RowNo() -1) AS tmp_Date

AUTOGENERATE

    $(vMaxDate) - $(vMinDate) + 1;

Note : In Master_Calendar i am Created "[Week in Month] " field as following

Master_Calendar:

LOAD

if(Year(tmp_Date)=WeekYear(tmp_Date), Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1, 

    Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1) AS [Week in Month]           //week number within a month, 1, 2, 3, 4, 5, 6

Resident temp_CALENDAR

ORDER BY tmp_Date ASC;

DROP TABLE tmp_CALENDAR;

OUTPUT : (Negative Weeks in Months) Eliminate the negative weeks in "[Week In Month] " Field.

   

Thanks

Madhu

[qlikmsg4u · Accepted solution]

try this

if(month(weekend(tmp_Date))= month(tmp_Date),div(day(WeekEnd(tmp_Date)),7),div(day(Weekend(tmp_Date,-1)),7)+1)+1 as WeekNumber

[madhubabum]

Hi  K N

Can i know the Root cause of the above problem , Because we got same calender in many applications.

Best Regards

Madhu