Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Qlikview Master Calendar Script Error

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);

error.PNG

     

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;

error 2.PNG

5 Replies

Re: Qlikview Master Calendar Script Error

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

Not applicable

Re: Qlikview Master Calendar Script Error

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

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

Re: Qlikview Master Calendar Script Error

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

Not applicable

Re: Qlikview Master Calendar Script Error

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

Not applicable

Re: Qlikview Master Calendar Script Error

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

Community Browser