Week in Month Calendar Problem

[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.

  

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

[settu_periasamy]

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

[pokassov]

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

[pokassov]

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