Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luizcdepaula
Creator III
Creator III

WeekYear giving wrong year

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

2016-06-21 20_07_35-Qlik Sense Desktop.png

View solution in original post

12 Replies
swuehl
MVP
MVP

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
Creator III
Creator III
Author

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
MVP
MVP

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;

2016-06-21 20_07_35-Qlik Sense Desktop.png

luizcdepaula
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
MVP
MVP

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

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

Autogenerate 31;

luizcdepaula
Creator III
Creator III
Author

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
MVP
MVP

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
Creator III
Creator III
Author

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.