Qlikview Master Calendar Script Error

[#1]

Hello Everyone,

Can Some One please help me with the following errors

Here is my Script for Master Calendar

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12);       

Temp: 

Load 

min (date([AE Start Date])) as minDate, 

max (date([AE Start Date])) as maxDate

Resident AE; 

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 

   date(TempDate) AS [AE Start Date], 

   week(TempDate) As Week, 

   Year(TempDate) As Year, 

   Month(TempDate) As Month,

   num( Month(TempDate)) as MonthNo,

   Month(TempDate)*100+Day(TempDate) as DayOfYear,

  // Month(TempDate)&'-'& Year(TempDate) as MonthYear,

   Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,

   Year(TempDate) &Num(Month(TempDate)) as YearMonth,

   //if(Year(TempDate)=WeekYear(TempDate), Week(TempDate)-Week(MonthStart(TempDate)) +1, Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,

   //'Week' & Ceil(Day(TempDate)/7) as WeekInMonth,

   Ceil(Day(TempDate)/7) as WeekInMonth,

   Day(TempDate) As Day, 

   //InYearToDate(TempDate, $(vToday), 0) *-1 as CurYTDFlag,

   //InYearToDate(TempDate, $(vToday), -1) *-1 as LastYTDFlag,

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekYear(TempDate)& Num(week(TempDate)) as YearWeek,

  WeekDay(TempDate) as WeekDay,

  Num(WeekDay(TempDate)+1) as WeekDayNo,

  Year2Date(TempDate, 0, 1, $(varMaxDate))*-1 AS CurYTD_Flag,

  Year2Date(TempDate,-1, 1, $(varMaxDate))*-1 AS   LastYTD_Flag,

  InMonthToDate(TempDate, $(varMaxDate),0)*-1 AS CurMTD_Flag,

  InMonthToDate(TempDate, $(varMaxDate),-1)*-1 AS LastMTD_Flag

  Resident TempCalendar

  Order By TempDate; 

Drop Table TempCalendar;

[sunny_talwar]

Check the [AE Start Date], it seems that it might not be interpreted as date by QlikView (Why don’t my dates work?)

[#3]

Also there is an extra parenthesis in the below line in MasterCalendar

Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,

[MarcoWedel]

maybe helpful, another calendar script without variables and temporary tables:

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabSourceOfDate;

regards

Marco

[#5]

Thank You. I tried to figure out My AE Date Format was incorrect as you said.

[#6]

Thank You Marco. The code looks simple. I will use it for my future projects.