Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ivandrago
Contributor 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

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

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

2 Replies

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

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

ivandrago
Contributor II

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

It works for me now for some reason!? Thanks

Community Browser