Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

double7671
Contributor

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;

Tags (2)
6 Replies

Re: master calendar question

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


talk is cheap, supply exceeds demand
double7671
Contributor

Re: master calendar question

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.

Re: master calendar question

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
double7671
Contributor

Re: master calendar question

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?

Re: master calendar question

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


talk is cheap, supply exceeds demand
double7671
Contributor

Re: master calendar question

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.

Community Browser