topic Re: master calendar question in App Development

master calendar question

Anonymous — Sun, 10 Jan 2016 00:50:26 GMT

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;

Re: master calendar question

Gysbert_Wassenaar — Sun, 10 Jan 2016 10:03:28 GMT

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

Re: master calendar question

Anonymous — Sun, 10 Jan 2016 14:43:38 GMT

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

Gysbert_Wassenaar — Sun, 10 Jan 2016 14:53:44 GMT

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

SET FirstWeekDay = 5;

Re: master calendar question

Anonymous — Sun, 10 Jan 2016 15:18:55 GMT

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

Gysbert_Wassenaar — Sun, 10 Jan 2016 15:48:13 GMT

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

Re: master calendar question

Anonymous — Sun, 10 Jan 2016 21:10:03 GMT

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.