topic Re: WeekYear gives wrong value when set ReferenceDay in Archived Groups

WeekYear gives wrong value when set ReferenceDay

rbecher — Wed, 23 Jul 2025 14:48:52 GMT

Hi all,

when I use these calendar settings:

SET FirstWeekDay=6; // Begin of week is Sunday
SET ReferenceDay=5; // 1st week of year if 5 days in January

the WeekYear() function gives the wrong year:

Calendar Script:

[MasterCalendar]:
LOAD 
    Date(TempDate) AS BookingDate, 
    week(TempDate) As Week,
    WeekYear(TempDate) as WeekYear,
    If(Week(TempDate)=1 AND Num(Month(TempDate))=12, Year(TempDate)+1, WeekYear(TempDate)) as WeekYearCorrected,
    Year(TempDate) As Year, 
    Month(TempDate) As Moth,
    Month(MakeDate(WeekYear(TempDate), If(Week(TempDate)=1, 1, If(Week(TempDate)>=52, 12, Month(TempDate))), 1)) As WeekMonth,    
    Day(TempDate) As Day, 
    WeekDay(TempDate) as Weekday,
    Date(TempDate, 'WWWW') as WeekDayLong
;

//=== Generate a temp table of dates === 
LOAD 
    date(mindate + IterNo()) AS TempDate,
    maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

// Test case
LOAD MakeDate(2007,11,30) as mindate,
MakeDate(2009,1,31) as maxdate
AutoGenerate(1);

I also added a workaround (field WeekYearCorrected) but would be better if this got fixed.

Ralf

Re: WeekYear gives wrong value when set ReferenceDay

sgrubic — Thu, 05 Apr 2018 10:32:10 GMT

Hey,

The official documentation states that the "function returns the year to which the week number belongs according to ISO 8601". ISO 8601 has Monday fixed as the first day of week, as far as I can tell. Sorry...

Re: WeekYear gives wrong value when set ReferenceDay

rbecher — Thu, 05 Apr 2018 10:49:18 GMT

It's right that WeekYear works according to ISO 8601, but this is very doubtful since then a variable ReferenceDay should not exist at all, or alternativly a WeekYearNonISO function should exist to have a consistent calendar behavior.

2nd, you're wrong with the Monday. ISO 8601 says first Thursday in January defines the first week.

Re: WeekYear gives wrong value when set ReferenceDay

rbecher — Thu, 05 Apr 2018 11:24:31 GMT

To make it more clear, all date or calendar functions should work according to the settings (FirstWeekDay, ReferenceDay, etc.) .

Who is helped when single functions work different, like WeekYear according to ISO.

Re: WeekYear gives wrong value when set ReferenceDay

sgrubic — Thu, 05 Apr 2018 12:29:40 GMT

No, I meant to say that Monday is always the first day of (any) week; overriding your statement:

SET FirstWeekDay=6; // Begin of week is Sunday

But yes, I completely agree that the behavior isn't consistent, and it (of course) should be.

Moreover, I just figured out that the official help for Week function‌ also claims that it works according to ISO 8601, but it behaves differently (as observed in your screenshot). And when I say "differently", I mean "consistent with other functions, except for WeekYear".

All in all, I think this should be filed as a bug.

Re: WeekYear gives wrong value when set ReferenceDay

swuehl — Thu, 05 Apr 2018 13:36:24 GMT

Ralf, I agree that Qlik should fix their HELP pages and make all calendar functions regard the system variables.

With regard to the proposed fix, I believe this code won't return the correct WeekYear e.g. for 2006-01-01 or for Week 53 /2008 in your sample setting.

Maybe use

If(Week(TempDate)=1, Year(WeekEnd(TempDate)), Year(Weekstart(TempDate))) as WeekYearCorrected2,

instead.

Re: WeekYear gives wrong value when set ReferenceDay

rbecher — Thu, 05 Apr 2018 13:51:40 GMT

Thanks, Stefan! This is much better..