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.
Solution 1: 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
Solution 2: Ceil((Day(tmp_Date)+Week Day(Month Start(tmp_Date)))/7) As [Week in Month]
But , Can i know the Root cause of the above problem , Because we got same calender in many applications.
Thanks
Madhu
Hi Madhu,
In your script,
if the year is not equal to WeekYear,
Week Actual - Week MonthStart +1 , e.g (1 - 49 +1) = -47
see the below screen shot.
same for, if Year = WeekYear (1-53+1) = -51
Hello!
Lets see through things.
As I can see WeekYear works something like that:
1. gets middle of the week icluding tmp_Date (thursday)
2. gets year for thursday.
For example, January, 1, 2011 is Sunday.
1. thursday for this week is December, 31, 2010
2. Year=2010
3. Week also will be calculated for December, 31, 2010 = 52
For the same reason we can get WeekYear for December, 30, 2014:
1. thursday for this week is January, 1, 2015
2. Year=2015
3. Week also will be calculated for January, 1, 2015 = 1
Please try this solution:
if(Week(tmp_Date)<Week(MonthStart(tmp_Date)),Week(MonthStart(tmp_Date)),0)+Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1