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;
