topic Re: WeekYear giving wrong year in QlikView

WeekYear giving wrong year

luizcdepaula — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: WeekYear giving wrong year

swuehl — Mon, 20 Jun 2016 22:28:35 GMT

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

Re: WeekYear giving wrong year

luizcdepaula — Tue, 21 Jun 2016 14:14:47 GMT

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,

Re: WeekYear giving wrong year

swuehl — Tue, 21 Jun 2016 18:08:23 GMT

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;

Re: WeekYear giving wrong year

luizcdepaula — Tue, 21 Jun 2016 21:22:42 GMT

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.

Re: WeekYear giving wrong year

luizcdepaula — Mon, 15 Aug 2016 19:35:31 GMT

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,

Re: WeekYear giving wrong year

swuehl — Mon, 15 Aug 2016 19:42:51 GMT

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

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

Autogenerate 31;

Re: WeekYear giving wrong year

luizcdepaula — Mon, 15 Aug 2016 19:56:18 GMT

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;

Re: WeekYear giving wrong year

swuehl — Mon, 15 Aug 2016 19:59:59 GMT

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)?

Re: WeekYear giving wrong year

luizcdepaula — Mon, 15 Aug 2016 20:04:30 GMT

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.

Re: WeekYear giving wrong year

swuehl — Mon, 15 Aug 2016 20:43:22 GMT

Can't see how you are linking this table or calculating the two other fields.

The calendar itself seems to work for me:

Re: WeekYear giving wrong year

luizcdepaula — Mon, 15 Aug 2016 21:26:12 GMT

Stefan,

It actually works fine. On our data source we are missing the first week of the year so it is not mapping.

I will work with the analyst to see how we can fill this gap.

Thank you for your help.

Re: WeekYear giving wrong year

gileswalker — Mon, 08 Feb 2021 23:08:45 GMT

Using: Qlik Sense

I was having the exact same issue with the erroneous year outcome, under the same SET conditions:

SET FirstWeekDay=6;
//Setting Sunday as the week start
SET BrokenWeeks=0;
// Broken Weeks not allowed
SET ReferenceDay=4;
//Jan 4th always belongs to week 1

Seems the weekyear function still has a bug/anomaly that I can't fathom, where the function displays the year of the first day of the new year as the year prior:

2020 in pink above should be 2021.

The way I fixed it was quite simple in the end. I have simply added a function the auto-calendar in the LOAD script ( I have also concatenated a week function to it for my own calendar):

The year part is simply:

weekyear(($1)+1)

So in the AutoCalendar the "$1" represents the date field in the app you have, and it's adding 1 day to the field, and then doing the weekyear logic from there.

I have done it, and then reconciled data outcomes to annual calendar transitions over several years, using this website as a link for the calendar matching my SET conditions, and its fine:

https://savvytime.com/week-number/united-states/2019

HTH someone else

Thanks

Giles