topic Re: Qlikview Master Calendar Script Error in QlikView

Qlikview Master Calendar Script Error

Wed, 16 Mar 2016 14:21:38 GMT

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;

Re: Qlikview Master Calendar Script Error

sunny_talwar — Wed, 16 Mar 2016 20:09:00 GMT

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

Re: Qlikview Master Calendar Script Error

Wed, 16 Mar 2016 21:51:44 GMT

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

MarcoWedel — Wed, 16 Mar 2016 23:15:35 GMT

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

Re: Qlikview Master Calendar Script Error

Thu, 17 Mar 2016 14:13:38 GMT

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

Re: Qlikview Master Calendar Script Error

Thu, 17 Mar 2016 14:14:25 GMT

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