Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get my reporting weeks set to use a Friday to Thursday week and then pull the week numbers out of given date fields. I have referenced the QV discussion located at https://community.qlik.com/blogs/qlikviewdesignblog/2014/01/27 and am using the SET coding of:
SET vCal_FD = 4; //First day of the week is Monday (0=Monday, 1= Tues,..., 6=Sun)
SET vCal_BW = 0; //Broken Weeks allowed {0=No, 1=Yes}
SET vCal_RD = 1; //Reference day = this day in January defines week one
In theory this would set Friday as the first day of the week, no broken weeks allowed, and ensures that January 1st is always in Week #1.
By this thinking, January 2016 should equate to:
Week 1 = Jan 1 (Friday) – 7 (Thursday)
Week 2 = Jan 8-14
Week 3 = Jan 15-21
Week 4 = Jan 22-28
Week 5 = Jan 29-Feb 4
I have a “CompletedDate” field that I am using and dates as a sample of:
1/13/2016 Should be Week 02 Ends up as Week 02 OK
1/14/2016 Should be Week 02 Ends up as Week 02 OK
1/15/2016 Should be Week 03 Ends up as Week 02 Wrong
1/16/2016 Should be Week 03 Ends up as Week 02 Wrong
1/18/2016 Should be Week 03 Ends up as Week 03 OK
1/25/2016 Should be Week 04 Ends up as Week 04 OK
1/26/2016 Should be Week 04 Ends up as Week 04 OK
1/29/2016 Should be Week 05 Ends up as Week 04 Wrong
1/31/2016 Should be Week 05 Ends up as Week 04 Wrong
2/1/2016 Should be Week 05 Ends up as Week 05 OK
My loading includes the code of “Right(WeekName(CompletedDate),2) as CompletedWeek”
I have tried several variations but it always seems that QV is mapping to a Monday to Sunday type week. What am I missing?
The standard QV date and time functions like WeekName() or Week() are unfortunately not impacted by the variables you defined. You would need to calculcate the week number manually as shown by Henric in his blog post:
Load *,
Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
Year( WeekYearRefDate ) as WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;
Load *,
Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;
The standard QV date and time functions like WeekName() or Week() are unfortunately not impacted by the variables you defined. You would need to calculcate the week number manually as shown by Henric in his blog post:
Load *,
Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
Year( WeekYearRefDate ) as WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;
Load *,
Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;
That was the right track, but since those all referenced each other I had to combine into a single long/odd formula.
I had to take the WeekRefDate formula and substitute into that into where the WeekYearRefDate formula calls for "WeekRefDate" and then take all that and substitute that into the WeekNumber formula where it called for "WeekYearRefDate".
I ended up with this formula that worked (I had a field named "CompletedDate" instead of the "Date" field above in your example:
Div( CompletedDate - WeekStart( (Date( YearStart( If( $(vCal_BW), CompletedDate, (Date( WeekStart( CompletedDate, 1, $(vCal_FD) ) - $(vCal_RD) )) )) + $(vCal_RD) - 1)), 0, $(vCal_FD) ) + 7, 7 ) as ReportingCompletedWeek
the WeekRefDate field corresponds to what exactly please ??