3 Replies Latest reply: Feb 15, 2017 11:32 AM by Anna fuksa RSS

    Master Calendar Flags

    james eckstein

         I am looking to make the following master Calendar Flags. The flags should cover a two year period. The Column2 flag should use the following logic. Flag everything two years prior to the last full quarter. Column1 should use the following logic, Flag everything two years prior to the second to last full quarter. The image should below shows the desired output.

       

      Column 3 shows what the column 2 flag should be after we reach the end of Q1 in 2017.    

       

       

      Screen Shot 2017-02-14 at 10.21.14 AM.png

       

      Thanks

        • Re: Master Calendar Flags
          Anna fuksa

          Could you post your app and your current solution ?

            • Re: Master Calendar Flags
              james eckstein

              I cannot post the app, but here is the script for the Master Calendar I am using.

               

               

              Temp_3;
              //Encounters;


              Let vMinDate = Num(Peek('MinDate',0,'MinMax'));
              Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

              //IMPORTANT NOTE: TODAY IS POINTING TO YESTERDAY, THE MAXIMIMUM DATA WITHIN THE APPLICATION SHOULD BE LIMITED TO THE LAST FULL DAY OF DATA (YESTERDAY)
              Let vToday = num(Today()-1);

              drop Table MinMax;



              //Autogenerate a source table for your calendar
              TempCalendar:
              Load
              $(vMinDate) + RowNo() - 1 as Num,
              Date($(vMinDate) + RowNo() - 1) as TempDate
              Autogenerate
              $(vMaxDate) - $(vMinDate) + 1;


              //Generate the Master Calendarå
              MasterCalendar:
              Load
              Autonumber('|'&Date(TempDate,'YYYYMM')) as DateNum,
              Date(Floor(TempDate)) as Date,
              Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,
              Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,
              Week(TempDate) as Week,
              Year(TempDate) as Year,
              AutoNumber(Date(YearStart(TempDate))) as YearID,
              chr(39) & right(Year(TempDate),2) as [Short Year],
              Month(TempDate) as Month,
              MonthStart(TempDate) as MonthStart,
              MonthEnd(TempDate) as MonthEnd,
              Year(Floor(TempDate)) &
              If(Len(Num(Month(Floor(TempDate))))=1, 0 &
              Num(Month(Floor(TempDate))),
              Num(Month(Floor(TempDate)))) as Period,
              Num(Month(TempDate), '00') as MonthNum,
              Day(TempDate) as Day,
              WeekDay(TempDate) as WeekDay,
              'Q' & ceil(Month(TempDate)/3) as Quarter,
              'Q' & ceil(Month(TempDate)/3)&'-'&
              Right(Year(TempDate),2) as QuarterYear,
              Date(monthstart(TempDate),'MMM-YY') as MonthYear,
              AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthYearID,
              //If(TempDate>$(vToday),1,0) as FutureFlag,
              If(TempDate>$(vToday),1,0) as FutureFlag,
              If( InMonth (TempDate, $(vToday), +1),1) as NextMonthFlag,
              Week(TempDate) & '-' & Right(Year(TempDate), 2) as WeekYear,
              Right(Year(TempDate), 2) & '-' & Week(TempDate) as YearWeek,
              inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
              inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,
              inmonthtodate(TempDate,$(vToday),0) * -1 as CurMTDFlag,
              inmonthtodate(TempDate,$(vToday),+1) * -1 as NextMTDFlag,
              inmonthtodate(TempDate,$(vToday),-1) * -1 as LastMTDFlag,
              If( InMonth (TempDate, $(vToday),0),1) as This_Month_Flag, // All Dates This Month This Year
              If( InMonth (TempDate, $(vToday),-1),1) as Last_Month_Flag, // All Dates This Month This Year
              If( InMonth (TempDate, $(vToday),-12),1,0) as LYMTDFlag,
              if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
              if(date(TempDate) >= Date(Today(0)-30), '1') as Rolling30Day,
              if(date(TempDate) = Date($(vToday)), '1') as PreviousDay,
              if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg,
              If(TempDate >= YearStart(Today(1), -1) and TempDate <= MonthEnd(Today(1), -12), 1, 0) as LY_YTD_Monthend,
              If(InMonthToDate (TempDate, $(vToday),-12),1) as MTD_LY_FLAG // All Dates This Month To Date Last Year
              Resident TempCalendar
              Order By TempDate ASC;

              //Delete temp table
              Drop Table TempCalendar;


              left join (MasterCalendar)
              load distinct CurMTDFlag
              , if(CurMTDFlag=0,'No','Yes') as Current_MTD_Flag
              Resident MasterCalendar
              ;


              left join (MasterCalendar)

              load Date 
              , NetWorkDays(Date+1,MonthEnd) as Proj_Remaining_Weekday_Days_In_Month //+1 logic is needed to not count current day
                , (num(floor(MonthEnd)) - num(floor(Date))) as Proj_Days_left_in_month
              , (num(floor(MonthEnd)) - num(floor(Date))) //Days left in month
                - NetWorkDays(Date+1,MonthEnd) //minus week days left in month
                as Proj_Remaining_Weekend_Days_In_Month
              Resident MasterCalendar
              where Date = Today()-1
              ;
               

            • Re: Master Calendar Flags
              Anna fuksa

              Please check,

               

              have commented your master calendar as it was easier for me