Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rohitraturi
New Contributor II

Problem with Calendar

Following is my script for the Appointment Calendar. I am getting an error as "Autogenerate: generate count is out of range". I am unable to decode the error.

LET v_DateFieldName = 'Appt Date';
LET v_CalendarFieldPrefix = 'Appt ';

AllDates:
LOAD
Num(Min([$(v_DateFieldName)])) as MinDate,
Num(Max([$(v_DateFieldName)])) as MaxDate
RESIDENT [Appointment_Fact_Mer];

LET v_MinDate = Peek('MinDate', 0, 'AllDates');
LET v_MaxDate = Peek('MaxDate', 0, 'AllDates');
LET v_Today = v_MaxDate;

DROP TABLE AllDates;

[$(v_CalendarFieldPrefix)Calendar]:
LOAD
Date(D,'M/D/YY') as [$(v_DateFieldName)],
Num(D) as [$(v_CalendarFieldPrefix)DateNum],
Date(D, 'DD-MMM') as [$(v_CalendarFieldPrefix)Day Month],

Y & '-' & Num(M) as [$(v_CalendarFieldPrefix)Year Month],
DUAL(Date(D,'MMM-YY'),MonthStart(D)) as [$(v_CalendarFieldPrefix)Month Year],

Y as [$(v_CalendarFieldPrefix)Year],
Month(YearStart(D)) & '-' & Month(YearEnd(D))& ' ' & Y AS [$(v_CalendarFieldPrefix)Year Description],
Y & 'Q' & Ceil(M / 3) as [$(v_CalendarFieldPrefix)Quarter],
Month(QuarterStart(D)) & '-' & Month(QuarterEnd(D))& ' ' & Y AS [$(v_CalendarFieldPrefix)Quarter Description],
Month(AddMonths(D,-2)) & '-' & Month(D)& ' ' & Y AS [$(v_CalendarFieldPrefix)Rolling 3mo],
Month(AddMonths(D,-11)) & '-' & Month(D)& ' ' & Y AS [$(v_CalendarFieldPrefix)Rolling 12mo],
MY as [$(v_CalendarFieldPrefix)Month],
Date(Floor(WeekEnd(D))) as [$(v_CalendarFieldPrefix)Week Ending Date],

'Q' & Ceil(M / 3) as [$(v_CalendarFieldPrefix)Quarter of Year],
M as [$(v_CalendarFieldPrefix)Month of Year],
Num(M) as [$(v_CalendarFieldPrefix)MonthNum], 
Week(D) as [$(v_CalendarFieldPrefix)Week of Year],
DayNumberOfYear(D) as [$(v_CalendarFieldPrefix)Day of Year], 
DayNumberOfQuarter(D) as [$(v_CalendarFieldPrefix)Day of Quarter], 
Day(D) as [$(v_CalendarFieldPrefix)Day of Month],

Y as [$(v_CalendarFieldPrefix)YearIndex],
(Y*4) + Ceil(M / 3) as [$(v_CalendarFieldPrefix)QuarterIndex],
AutoNumber((Y*12) + Num(M)) as [$(v_CalendarFieldPrefix)MonthIndex],
((Y-1)*12) + Num(M) as [$(v_CalendarFieldPrefix)PrevYearMonthIndex],
If(Date(Floor(WeekEnd(D))) = Peek([$(v_CalendarFieldPrefix)Week Ending Date]), Peek([$(v_CalendarFieldPrefix)WeekIndex]), Alt(Peek([$(v_CalendarFieldPrefix)WeekIndex]),0)+1) as [$(v_CalendarFieldPrefix)WeekIndex],
Num(D) as [$(v_CalendarFieldPrefix)DateIndex],

Weekday(D) as [$(v_CalendarFieldPrefix)Weekday],
Dual(Left(Weekday(D), 1), D - WeekStart(D)) as [$(v_CalendarFieldPrefix)Weekday (D)],
Dual(Weekday(D) & ' ' & Date(D),D) as [$(v_CalendarFieldPrefix)Weekday Date],
if(D < today(), 1, 0) as [$(v_CalendarFieldPrefix)Date Elapsed Flag],
if(D < monthstart(today()), 1, 0) as [$(v_CalendarFieldPrefix)Month Elapsed Flag]

;
LOAD
Date($(v_MinDate) + RecNo() - 1) as D,
Year($(v_MinDate) + RecNo() - 1) as Y,
Month($(v_MinDate) + RecNo() - 1) as M,
Date(MonthStart($(v_MinDate) + RecNo() - 1), '$(MonthFormat)') as MY
AUTOGENERATE v_MaxDate - v_MinDate + 1;


Exit Script

2 Replies
MVP
MVP

Re: Problem with Calendar

AUTOGENERATE $(v_MaxDate) - $(v_MinDate) + 1;

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rohitraturi
New Contributor II

Re: Problem with Calendar

 Tried this but got an error as generate count is negative