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
;
