Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ivandrago
Creator II
Creator II

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

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;

1 Solution

Accepted Solutions
Nicole-Smith

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

View solution in original post

2 Replies
Nicole-Smith

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

ivandrago
Creator II
Creator II
Author

It works for me now for some reason!? Thanks