2 Replies Latest reply: Jan 6, 2014 12:09 PM by Ivan Drago RSS

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

    Ivan Drago



      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.


      LOAD min([InvoiceDate]) as Oldest 

      //Calendar Range
      LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),
            monthstart(addmonths($(vToday),-13))-1);  // the -1 makes loop easier
      LET vEndDate = num(yearend(today()));

      SET vCompStartDate = monthstart(addmonths(today(),-14));

      DROP TABLE [StartDate];

      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
      WHILE $(vStartDate) + IterNo() <= $(vEndDate);

      [Analysis Periods]:
      LOAD monthstart(addmonths($(vCompStartDate), 1*IterNo())) as [Month Start],
        IterNo() as [Analysis Period]
      WHILE IterNo() <= 14;