WeekYear gives wrong value when set ReferenceDay

[rbecher]

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

[sgrubic]

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

[rbecher]

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.

[rbecher]

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.

[sgrubic]

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

1. 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.

[swuehl]

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.

[rbecher]

Thanks, Stefan! This is much better..