Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi K N
Can i know the Root cause of the above problem , Because we got same calender in many applications.
Best Regards
Madhu