master calendar question

[Anonymous]

Hello, My sales report is weekly based. A week starts every Saturday through Friday.

Based on above background, I need build up a master calendar with YTD feature. I don't need date, day, but week and year.

Below is my script, but it does not work.I don't know how to have the TempWeek turn to '201501' after it reaches '201452'. Can someone help to fix it? Thanks in advance.

Let varMinWeek = Num('201401'); 
Let varMaxWeek =Num('201652');
 
TempCalendar: 
LOAD 
               $(varMinWeek) + Iterno()-1 As Num, 
               ($(varMinWeek) + IterNo() - 1) as TempWeek 
               AutoGenerate 1 While $(varMinWeek) + IterNo() -1 <= $(varMaxWeek); 
 
[Master Calendar]: 
Load

               right(TempWeek, 2) As [Week],
               left(TempWeek, 4) as [Year],
               YeartoDate(TempWeek)*-1 as [CurYTDFlag], 
               YeartoDate(TempWeek,-1)*-1 as [LastYTDFlag],
               YeartoDate(TempWeek,-2)*-1 as [Last Before Year Flag]
              
Resident TempCalendar 
Order By TempWeek ASC;

Drop Table TempCalendar;

[Gysbert_Wassenaar]

You'll have to use the weekstart dates instead. See attached example.

[Anonymous]

Thanks Gysbert. I saw it shows 201353 instead of 201401. I am guessing because my fiscal year does NOT starts Jan 1.

Actually it starts Feb 1. A week starts Saturday through Friday. so,

2014 wk 1 is "2014-02-01" - "2014-02-07"

2014 wk 2 is "2014-02-08' - "2014-02-14"

and so on. Could you please help to update the script? Thanks again.

[Gysbert_Wassenaar]

Set the variable that declares what the first day of the week is to 5 for Saturday:

SET FirstWeekDay = 5;

[Anonymous]

I did add set firstweekday=5, but nothing changed.

do I have to change something else, like first date of fiscal year is feb 1?

[Gysbert_Wassenaar]

If your fiscal year starts in February you also need this one: Set FirstMonthOfYear=2;

[Anonymous]

I changed script a bit. here are two things not working,

1, Pic1, the first 4 lines dates are correct. but week should be 201350, 201351, 201352, 201353 respectively.

2. Pic2, the week 201453 is supposed to be 201501,

3. same pic2, I set varMaxWeek = Num(MakeWeekDate(2016,52,4)), I think the max date on calendar should be 2016/01/31, but it only shows 2015/02/21 as max date, why?

As a green hand, I knew some questions are silly, really appreciate they could be fixed.

my latest qvf file was also attached here. Thanks for your time.