topic Re: Mapping dates to weeks and setting week start/end days in QlikView

Mapping dates to weeks and setting week start/end days

dawgfather — Thu, 11 Feb 2016 20:57:55 GMT

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?

Re: Mapping dates to weeks and setting week start/end days

swuehl — Thu, 11 Feb 2016 23:28:49 GMT

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 ;

Re: Mapping dates to weeks and setting week start/end days

dawgfather — Fri, 12 Feb 2016 00:47:50 GMT

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

Re: Mapping dates to weeks and setting week start/end days

MauriceLudo — Tue, 21 Nov 2017 13:25:43 GMT

the WeekRefDate field corresponds to what exactly please ??