I'm having a little bit of trouble with my master calendar script. I've reused this script in many of my applications without any trouble. Today I was developing a new application and it threw a script error after I inserted the master calendar code. A picture of the error is attached and my code is below. I don't understand why Qlik thinks there is a field in this part of the script. Has anyone ran into something similar to this? Thanks in advance!
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & if (Ceil (rowno()/3) = 4, 1, Ceil (rowno()/3) + 1) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([OrderDate]) as minDate,
max([OrderDate]) as maxDate
Resident eComOrders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS CalendarDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
Okay most of the solutions you guys provided gave me my minimum and maximum dates and fixed my original error so thank you all! After fixing that I ran into another problem. In a straight table my dates and times weren't grouping by the minute so I had to use the formula below to get rid of the seconds in my timestamp. Is there a better way to do this than the formula below? The formula is working fine, but i was just curious.
TIMESTAMP(FLOOR([OrderDate]*1440)/1440) AS [OrderDate]
