I have a calendar using the script below
MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Facts;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
/* 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
Disconnected during the Date Island exercise by renaming TempDate as IslandDate
*/
MasterCalendar:
LOAD
//TempDate as IslandDate,
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), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
Max date in my data is 19/11/2013 so I would like any transactions with a date after 19/12/2012 not to be flagged as LastYTDFlag 1 but 0
Dates up to 31/12/2012 are being flagged as 1 but they should be 0
The date filed in my data is loaded as
inception_date_tpi as Date,
Can anyone adice what I need to change in My Calendar for this to work.
