Hello All,
I am trying to create a variable in my script for a rolling 12 calculation. I thought I had this figured out, but I cannot get it to work. See my calendar script below, any insight would be much appreciated.
//Calendar Tab
/*************** MinMax Table *************
Keeps minimum and maximum Date value from Facts/ Salesdata table
*/
MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Salesdata;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
let vMaxDay = '=day(max(Date))';
let vMaxMonth = '=month(max(Date))';
let vMaxYear = '=max(Year)';
let vPriorMonth = '=month(addmonths(max(Date),-1))';
let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';
let vPriorYear = '=vMaxYear-1';
let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';
let vPriorYear2 = '=vMaxYear-2';
/*************** Temporary Calendar *************
Generates a single table with one field containing
all existing dates between MinDate and MaxDate.
*/
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
/*************** Master Calendar ****************/
MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,
If(Num((TempDate))>=(Num(MonthStart(date($(varMaxDate)),-12))),1,0) as R12Flag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
