Mapping dates to weeks and setting week start/end days

[dawgfather]

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 1^st 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?

[swuehl · Accepted solution]

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 ;

[dawgfather]

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

[MauriceLudo]

the  WeekRefDate  field corresponds to what exactly please ??