topic Re: Week in Month Calendar Problem in QlikView

Week in Month Calendar Problem

madhubabum — Fri, 24 Jul 2015 06:43:26 GMT

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

Re: Week in Month Calendar Problem

settu_periasamy — Fri, 24 Jul 2015 07:43:16 GMT

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

Re: Week in Month Calendar Problem

pokassov — Fri, 24 Jul 2015 07:46:56 GMT

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

Re: Week in Month Calendar Problem

pokassov — Fri, 24 Jul 2015 08:06:45 GMT

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