Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Just looking at the 'Filtered' year in the script, here is what might be the problem:
In the script you have these two lines:
IF(((Year(Date)<= Year(Today())) and (Year(Date)>2007)),Year(Date),Null()) as FilteredYear,
If(Year(Today()) = FilteredYear....etc etc etc
You can't reference 'FilteredYear' in the line below where you are aliasing it. Replace 'FilteredYear' in the second line with the value of Filtered Year:
If(((Year(Date)<= Year(Today())) and (Year(Date)>2007)),Year(Date),Null())
Hope that helps!
Can you post the Document Log? That would be much easier to understand.
-Rob
I'd be glad to--where can one find the document log? Thanks!
Ben
You have to check the option in document properties that it is enabled or not by Click Settings >> Document Properties >> General Tab >> Check the "Generate Log File" Option checked or not if not then checked it and generate the log file by reloading the applicaion.
Just looking at the 'Filtered' year in the script, here is what might be the problem:
In the script you have these two lines:
IF(((Year(Date)<= Year(Today())) and (Year(Date)>2007)),Year(Date),Null()) as FilteredYear,
If(Year(Today()) = FilteredYear....etc etc etc
You can't reference 'FilteredYear' in the line below where you are aliasing it. Replace 'FilteredYear' in the second line with the value of Filtered Year:
If(((Year(Date)<= Year(Today())) and (Year(Date)>2007)),Year(Date),Null())
Hope that helps!
Thanks, that worked!