Calendar fixed start date

ivandrago — Mon, 16 Dec 2013 17:50:02 GMT

Hi,

I have the following calendar script, I want the StartDate to always be the 01/01/2011 but not shure how to do this?

[StartDate]:
LOAD min([InvoiceDate]) as Oldest
FROM

(qvd);

//Calendar Range
LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
peek('Oldest')-1,
monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier
LET vEndDate = num(yearend(today()));

SET vCompStartDate = monthstart(addmonths(today(),-14));

DROP TABLE [StartDate];

Calendar:
LOAD Date,
year(Date) as Year,
month(Date) as Month,
monthstart(Date) as [Month Start],
'W'&week(Date) as Week,
month(Date)&'-'&Year(Date) as [Month Year],
'Q'&Ceil(Month(Date)/3) as Quarter,
QuarterName(Date) as [Quarter Name],
   day(Date) as Day,
weekday(Date) as Weekday,
weekday(Date)&' '&day(Date) as [Day of Week],
if(Date=$(vToday), 1) as [CD Flag],
// if(Date=($(vToday)-1),1)as [PD Flag],
if(Date=($(vToday)-1),1)as [PD Flag 2],
if(year(Date)=year($(vToday)),1) as [CY Flag],
if(year(Date)=year(addyears($(vToday),-1)),1)as [PY Flag],
If((Date>=YearStart($(vToday)) and Date<=$(vToday)), 1) as [CYTD Flag],
If((Date>=YearStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYTD Flag],
if(QuarterName(Date)=QuarterName($(vToday)),1) as [CQ Flag],
If((Date>=QuarterStart($(vToday)) and Date<=$(vToday)), 1) as [CQTD Flag],
If((Date>=QuarterStart(AddMonths($(vToday),-3)) and Date<=AddMonths($(vToday),-3)), 1) as [PQTD Flag],
If((Date>=QuarterStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYQTD Flag],
If((Date>=MonthStart($(vToday)) and Date<=MonthEnd($(vToday))), 1) as [CM Flag],
If((Date>=MonthStart($(vToday)) and Date<=$(vToday)), 1) as [CMTD Flag],
If((Date>$(vToday) and Date<=MonthEnd($(vToday))), 1) as [RCM Flag],
If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=AddMonths($(vToday),-1)), 1) as [PMTD Flag],
If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [PM Flag],
If((Date>=MonthStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYMTD Flag],
If((Date>=MonthStart(AddMonths($(vToday),-3)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R3 Flag],
If((Date>=MonthStart(AddMonths($(vToday),-12)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R12 Flag],
If((Date>=MonthStart(AddMonths($(vOneYearAgo),-3)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R3 Flag],
If((Date>=MonthStart(AddMonths($(vOneYearAgo),-12)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R12 Flag],
If((Date>=WeekStart($(vToday)) and Date<=WeekEnd($(vToday))), 1) as [CW Flag],
If((Date>=WeekStart($(vToday)-7) and Date<=WeekEnd($(vToday)-7)), 1) as [PW Flag],
If((Date>=WeekStart($(vToday)) and Date<=$(vToday)), 1) as [CWTD Flag],
If((Date>$(vToday) and Date<=WeekEnd($(vToday))), 1) as [RCW Flag],
If(Year(Date)>=year(addmonths(today(),-13)),Year(Date)) as [New Year];

LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate);

[Analysis Periods]:
LOAD monthstart(addmonths($(vCompStartDate), 1*IterNo())) as [Month Start],
IterNo() as [Analysis Period]
AUTOGENERATE 1
WHILE IterNo() <= 14;

Re: Calendar fixed start date

Gysbert_Wassenaar — Mon, 16 Dec 2013 18:05:08 GMT

Change

LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),

peek('Oldest')-1,

monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier

LET vStartDate = num(date#('01/01/2011','DD/MM/YYYY'));

Re: Calendar fixed start date

Anonymous — Mon, 16 Dec 2013 18:17:22 GMT

I prefer it shorter:

LET vStartDate = num(makedate(2011));

Re: Calendar fixed start date

Gysbert_Wassenaar — Tue, 17 Dec 2013 13:38:34 GMT

Me too, but this way the OP should be able understand how to use any start date, not just first of Januaries.

Re: Calendar fixed start date

Anonymous — Tue, 17 Dec 2013 15:35:52 GMT

Gysbert,

Can't disagree with this. That's why I didn't use the shortest possible:

LET vStartDate = 40544;

Anyway, the makedate() has its advantage - it doesn't depend on the date format. So, this maybe a better way - has year, month, and day, and format-independent at the same time:

LET vStartDate = num(makedate(2011,1,1));

Regards,

Michael

topic Re: Calendar fixed start date in QlikView

Calendar fixed start date

Re: Calendar fixed start date

Re: Calendar fixed start date

Re: Calendar fixed start date

Re: Calendar fixed start date