Dear Community,
I've got a flag that I just put in a new field (FilteredYear) and when I do a reload, I get a field not found error with FilteredYear. (I think). Why is this, and how can I fix it?
Ben
If(Year(Today())=FilteredYear And Num(Month(Today())-1)=Num(Month(MaxAsOfDate)),1,
If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,
The full calendar script is below.
--------------------------------------------------------------------------------------
MAPPING LOAD
Num(CalendarExclusions.Date) as CalendarExclusions.Date,
CalendarExclusions.WorkingDayCounter
FROM C:\QlikView\Development\QVD\Historical Analysis\SalesQVDLoad\CalendarExclusions.qvd (qvd);
Set vDateSourceTable = 'LinkTable';
Let vToday = num(Today());
TempCalendar:
Load Distinct
Date(Daystart(Key|Date),'M/DD/YYYY') AS Date
Resident $(vDateSourceTable);
TempAccountValue:
outer join (TempCalendar)
Load
Max(AccountValue.AsOfDate) as MaxAsOfDate
Resident AccountValue;
Calendar:
LOAD Distinct
Date(Daystart(Date),'M/DD/YYYY') AS Key|Date,
Date,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),0) as WorkingDayFlag,
// IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), Date(Daystart(Date),'M/DD/YYYY')),Null()) as WorkingDayDate,
// Basic Date Dimensions
Year(Date) AS Year,
Year(Date) - 1 AS LYear,
Month(Date) AS Month,
Num(Month(Date)) AS MonthNumber,
// If(Year(Today())=Year(Date) And Num(Month(Today()))= $(vMaxAsOfDate),1,
IF(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null()) as FilteredYear,
If(Year(Today())=FilteredYear And Num(Month(Today())-1)=Num(Month(MaxAsOfDate)),1, // Num(Month(Date))
If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag, //Flag for Sales Dash, originally to ignore selections on listboxes
Week(Date) as WeekNumber,
Text(Date(monthstart(Date), 'MMM-YYYY')) AS MonthYear,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),Null()) * week(Date) as Week,
// Text(Date(monthstart(Date), 'YYYY')) & '-' & week(Date) AS YearWeek,
If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) AS Quarter,
If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) & '-' & Year(Date) AS QuarterYear,
Ceil(Day(Date)/7,1) as MonthWeekNumber,
IF(Ceil(Day(Date)/7,1)=1, '.6',
IF(Ceil(Day(Date)/7,1)=2, '.85',
IF(Ceil(Day(Date)/7,1)=3, '.85',1))) as Factor,
// week(Date) AS Week,
weekday(Date) AS Weekday,
day(Date) AS Day,
date(Date, 'MM/DD') AS DateMMDD,
If(Date< AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-0)
and Date> AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-12)-1,1,0) as L12M,
If(Date< num($(vToday)) and Date> AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-12)-1,1,0) as R12M,
// Year flags
inyear(Date, $(vToday), 0) * -1 AS CY, //Current year
inyear(Date, $(vToday), -1) * -1 AS FPY, //First prior year
inyear(Date, $(vToday), -2) * -1 AS SPY, //Second prior year
inyear(Date, $(vToday), 1) * -1 AS NY, //Next year
inyear(Date, $(vToday), -1) * -1 AS LY, //Last year (Same as FPY)
// Year-to-date flags
inyeartodate(Date, $(vToday), 0) * -1 AS CYTD, //Current year-to-date
inyeartodate(Date, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date
inyeartodate(Date, $(vToday), -2) * -1 AS SPYTD, //Second prior year-to-date
inyeartodate(Date, $(vToday), 1) * -1 AS NYTD, //Next year
// Quarter flags
inquarter(Date, $(vToday), 0) * -1 AS CQ, //Current quarter
inquarter(Date, $(vToday), -4) * -1 AS FPQ, //First prior quarter, same quarter last year
inquarter(Date, $(vToday), -8) * -1 AS SPQ, //Second prior quarter, same quarter two years ago
inquarter(Date, $(vToday), -1) * -1 AS LQ, //Last Quarter, 1 quarter ago
inquarter(Date, $(vToday), -2) * -1 AS SLQ, //Second Last Quarter, 2 quarters ago
inquarter(Date, $(vToday), -3) * -1 AS TLQ, //Third Last Quarter, 3 quarters ago
inquarter(Date, $(vToday), 1) * -1 AS NQ, //Next quarter
inquarter(Date, $(vToday), -1) * -1
+ inquarter(Date, $(vToday), -2) * -1 AS L2Q, //Last 2 Quarters
// Quarter-to-date flags
inquartertodate(Date, $(vToday), 0) * -1 AS CQTD, //Current quarter-to-date
inquartertodate(Date, $(vToday), -4) * -1 AS FPQTD, //First prior quarter-to-date, same quarter last year
inquartertodate(Date, $(vToday), -8) * -1 AS SPQTD, //Second prior quarter-to-date, same quarter two years ago
inquartertodate(Date, $(vToday), -1) * -1 AS LQTD, //Last Quarter, 1 quarter ago
inquartertodate(Date, $(vToday), -2) * -1 AS SLQTD, //Second Last quarter-to-date, 2 quarters ago
inquartertodate(Date, $(vToday), -3) * -1 AS TLQTD, //Third Last quarter-to-date, 3 quarters ago
inquartertodate(Date, $(vToday), 1) * -1 AS NQTD, //Next quarter-to-date
// Month flags
inmonth(Date, $(vToday), 0) * -1 AS CM, //Current month
inmonth(Date, $(vToday), -1) * -1 AS LM, //Last month
inmonth(Date, $(vToday), -12) * -1 AS FPM, //First prior month, same month last year
inmonth(Date, $(vToday), -24) * -1 AS SPM, //Second prior month, same month two years ago
inmonth(Date, $(vToday), 1) * -1 AS NM, //Next month
inmonth(Date, $(vToday), 2) * -1 AS SNM, //Second Next month, 2 Months in the future
inmonth(Date, $(vToday), 3) * -1 AS TNM, //Third Next month, 3 Months in the future
inmonth(Date, $(vToday), 4) * -1 AS FNM, //Fourth Next month, 4 Months in the future
// Month-to-date flags
inmonthtodate(Date, $(vToday), 0) * -1 AS CMTD, //Current month-to-date
inmonthtodate(Date, $(vToday), -1) * -1 AS LMTD, //Last month-to-date
inmonthtodate(Date, $(vToday), -12) * -1 AS FPMTD, //First prior month-to-date, same month last year
inmonthtodate(Date, $(vToday), -24) * -1 AS SPMTD, //Second prior month-to-date, same month two years ago
// Months in Current Quarter flags
IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=1,1,0) as CQM1,
IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=2,1,0) as CQM2,
IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=0,1,0) as CQM3,
// Months in Next Quarter flags
IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=1,1,0) as NQM1,
IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=2,1,0) as NQM2,
IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=0,1,0) as NQM3,
// Week flags
inweek(Date, $(vToday), 0) * -1 AS CW, //Current week
inweek(Date, $(vToday), -1) * -1 as LW, //Last week
Inweek(Date, $(vToday), -52) * -1 as FPW, //Same week last year
// Week-to-date flags
inweektodate(Date, $(vToday), 0) * -1 AS CWTD, //Current week-to-date
// Day Flags
if(date(Date) = date($(vToday)), 1,0) as CD,
if(date(Date) = date($(vToday)-1), 1,0) as Yesterday,
if(date(Date) > date($(vToday)), 1,0) as FutureDate,
IF(Date<= Num(AddMonths(MonthEnd(Today()),-3)),1,0) as [90DayFlag]
Resident TempCalendar;
Drop Table TempCalendar;
