WeekYear giving wrong year

[luizcdepaula]

Hello experts,

I believe that QlikSense might have an issue with the Master Calendar script.

I am applying the set variables below so that I set the week that contains the 4th day of January as the week 1. So the variables are set as below:

SET FirstWeekDay=6; (Sunday)

SET BrokenWeeks=0; (Unbroken Weeks)

SET ReferenceDay=4; (contain day 4 of January)

It supposed to work fine, but my results are giving me the date of the first Sunday of the year that contains the day 4 of January with the previous year (2015)

This is causing the fact data to recognize the week that starts on 1/10/2016 as the first Sunday of year 2016, and therefore the first week of the year. All my results are one week off.

Is there a bug in Qlik or it is a problem with the Master Calendar script?

Thanks in advance,

LD

[swuehl]

I think it's a bug in WeekYear() function assuming that it should consider the new variables.

I believe you can calculate the WeekYear manually using the code HIC posted some time ago:

Redefining the Week Numbers

[luizcdepaula]

Swuehl,

Apparently it is a bug with the WeekYear() function. I applied the changes from the HIC post Redefining the Week Numbers, but the year 2015 for the first day of the week 1 is still showing. Any other suggestions?

Thanks,

LD

[swuehl · Accepted solution]

The code worked for me:

Set vCal_FD = 6; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}

Load *,

      Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

      Year( WeekYearRefDate ) as WeekYear2;

Load *,

      Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;

Load *,

      Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;

LOAD Week(Date) as Week,

WeekYear(Date) as WeekYear,

Date,

Weekday(Date) as Weekday;

LOAD Date(Makedate(2016)+recno()-1) as Date

Autogenerate 31;

[luizcdepaula]

Swuehl,

That did the work! I just added the script you built with the current Master Calendar I had already.

Just adding your script, created a separate table with new date dimension, including the new WeekYear2. I just need to figure out the best way to combine it with the original Master Calendar.

Any ideas.

LD

[luizcdepaula]

Hi Stefan,

I am using the script you provided me. It is giving me only giving me records for Jan 2016 for WeekYear2. From February on, it is null. The subset ration is 2.87%. Any suggestions?

I added your script in the end of the master calendar script and commented out the initial Week and WeekYear from Master Calendar.

Thanks,

LD

[swuehl]

Have you adapted the date range generations to the date range you require?

LOAD Date(Makedate(2016)+recno()-1) as Date

Autogenerate 31;

[luizcdepaula]

I just noticed that, I updated for the whole year with 356 and it worked.

The only thing that I am having trouble with is that the date is not aligning the weekyear with the new script, so I am still getting Jan 10, 2016 as the first Sunday of the year, instead of Jan 3rd. I added you script to my master calendar as below. Please let me know if I should make any changes as this is preventing me from moving forward with this project. Thanks.

// generate calendar table

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12);

let varMinDate = 41275;

Let varMaxDate = num(yearend(AddYears(today(),0)));

//Let varMaxDate = 44000;

//above was "2" but gave two years in advanced.  CHanges to 0 for current year only

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS "Date", 

//                week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day,

               if(TempDate=today(),1,

                 YeartoDate(TempDate)*-1) as CurYTDFlag, 

               if(TempDate=addyears(today(),-1),1,

                   YeartoDate(TempDate,-1)*-1) as LastYTDFlag, 

               //YearToDate(TempDate,0,Month(today()))*-1 as CurMTDFlag,

               //YearToDate(TempDate,-1,Month(today()))*-1 as LastMTDFlag,

               if(Month(TempDate)=Month(today()) and Year(TempDate)=Year(today()),1,-1) as CurMTDFlag,

               if(Month(TempDate)=Month(today()-1) and Year(TempDate)=Year(today()-1),1,-1) as CurMTDFlag_1,

               if(Month(TempDate)=Month(today()-2) and Year(TempDate)=Year(today()-2),1,-1) as CurMTDFlag_2,

               if(Month(TempDate)=Month(AddMonths(today(),1)) and Year(TempDate)=Year(AddMonths(today(),1)),1,-1) as NextMonthFlag,

               if(Month(TempDate)<=Month(AddMonths(today(),-1)) and Year(TempDate)=Year(AddMonths(today(),1)),1,-1) as PrevMonthFlag,

               if(TempDate>=(today()-16) and (TempDate<=today()-1),1,0) as LastTwoWeekFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

//                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

//                WeekDay(TempDate) as WeekDay,

               If(Month(TempDate)>=Month(Today()) and Year(TempDate)=Year(today()),1,-1) as CurMTDandGreaterFlag

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

//lib://SharedDataExtract/MasterCalendar.qvd

// store into qvd

//STORE MasterCalendar INTO [lib://SharedDataTransform/MasterCalendar.qvd] (qvd);

//DROP TABLE MasterCalendar;

//Exit Script;

Load *,

      Div( Date - WeekStart( WeekYearRefDate, 0, $(FirstWeekDay) ) + 7, 7 ) as WeekNumber,

      Year( WeekYearRefDate ) as WeekYear2;

Load *,

      Date( YearStart( If( $(BrokenWeeks), Date, WeekRefDate )) + $(ReferenceDay) - 1) as WeekYearRefDate ;

Load *,

      Date( WeekStart( Date, 1, $(FirstWeekDay) ) - $(ReferenceDay) ) as WeekRefDate ;

LOAD Week(Date) as Week,

WeekYear(Date) as WeekYear,

Date,

Weekday(Date) as Weekday;

LOAD Date(Makedate(2016)+recno()-1) as Date

Autogenerate 356;

[swuehl]

Are you still using these initial settings from your OP?

"I am applying the set variables below so that I set the week that contains the 4th day of January as the week 1. So the variables are set as below:

SET FirstWeekDay=6; (Sunday)

SET BrokenWeeks=0; (Unbroken Weeks)

SET ReferenceDay=4; (contain day 4 of January)

"

Then I guess what you see is what you want(ed)?

[luizcdepaula]

yes, just like the post. But one of the Calendar Year/Week from a query is still linking the date to the second week of the year. I get no first week assigned.