Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Change
LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
peek('Oldest')-1,
monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier
to
LET vStartDate = num(date#('01/01/2011','DD/MM/YYYY'));
Change
LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
peek('Oldest')-1,
monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier
to
LET vStartDate = num(date#('01/01/2011','DD/MM/YYYY'));
I prefer it shorter:
LET vStartDate = num(makedate(2011));
Me too, but this way the OP should be able understand how to use any start date, not just first of Januaries.
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