Hi,
I have the following calendar script, I want the EndDate to always be the last day of the current Month?
I did try changing it to the following LET vEndDate = num(MonthEnd(today())); but didn't work.
[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;
