5 Replies Latest reply: Mar 17, 2016 10:13 AM by sahastra ramesh

# Qlikview Master Calendar Script Error

Hello Everyone,

Here is my Script for Master Calendar

QuartersMap:

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

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:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

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

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

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

• ###### 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:
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;
While MinDate+IterNo()-1 <= MaxDate;
Max(Date) as MaxDate
Resident tabSourceOfDate;
```

regards

Marco

• ###### Re: Qlikview Master Calendar Script Error

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