I need a flag in my master calendar that flags all dates in the last 6 months from the last full month.
Example today is March 8, 2017. So the last full month is February 2017. I would want this flag to flag all dates in the months
September 2016-February 2017
If the todays date is April 1st I would want the flag to be from
October 2016-March 2017.
Here is my calendar script:
MinMax:
LOAD
Min([ReportingDate]) as MinDate,
//max([ReportingDate]) as MaxDate // use the reporting date as a reference for the month to display as prior
today() as MaxDate //use today's date as a reference for the month to display as prior
Resident
Fact;
Let vMinDate = Num(Peek('MinDate',0,'MinMax'));
Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
Let vToday = num(Today());
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 [ReportingDate],
Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,
Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,
Week(TempDate) as Week,
Year(TempDate) as Year,
chr(39) & right(Year(TempDate),2) as [Short Year],
Month(TempDate) as Month,
AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthID,
MonthStart(TempDate) as MonthStart,
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,
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,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(AddMonths(Today(0),-6)), '1') as Rolling6Mo,
if(date(TempDate) >= Date(Today(0)-7), '1') as Rolling7Day,
if(date(TempDate) = Date(Today(0)-2), '1') as PreviousDay,
if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg,
DayNumberOfYear(TempDate) as DayNumberOfYear,
WeekStart( TempDate, 0, 5 ) as WeekStartSaturday,
WeekEnd( TempDate, 0, 5 ) as WeekEndFriday,
WeekStart( TempDate, 0, 5 ) & ' - ' &
WeekEnd( TempDate, 0, 5 ) as WeekInterval
Resident TempCalendar
Order By TempDate ASC;
//Delete temp table
Drop Table TempCalendar;
Thanks in advance
