Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
AUTOGENERATE $(v_MaxDate) - $(v_MinDate) + 1;
Tried this but got an error as generate count is negative