Calendar fixed End Date to show last day of the current month

ivandrago — Mon, 06 Jan 2014 16:51:37 GMT

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;

Re: Calendar fixed End Date to show last day of the current month

Nicole-Smith — Mon, 06 Jan 2014 16:57:59 GMT

It works for me. See the attached (watered down) example.

Re: Calendar fixed End Date to show last day of the current month

ivandrago — Mon, 06 Jan 2014 17:09:49 GMT

It works for me now for some reason!? Thanks

topic Calendar fixed End Date to show last day of the current month in QlikView

Calendar fixed End Date to show last day of the current month

Re: Calendar fixed End Date to show last day of the current month

Re: Calendar fixed End Date to show last day of the current month