Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

master calendar question

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;

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.