Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone spot where I am going wrong with this 'Close Date' Calendar? CurYTDFlag is coming back with only a zero for each calendar record -- there is no '1' for 2014 records.
// Calendar for Close Date
MinMax:
LOAD
Min("Close Date") as MinDate,
Max("Close Date") as MaxDate
Resident Opportunities;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = Today();
Drop Table MinMax;
//*********************Temp Calendar*************
TempCal2:
LOAD
date($(vMinDate) + RowNo() - 1) AS TempDate2
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;
//******************** MASTER CALENDAR *************************
MasterCalendar2:
Load
TempDate2 AS "Close Date",
Year(TempDate2) as YearClosed,
Month(TempDate2) as MonthClosed,
Day(TempDate2) as DayClosed,
'Q' & Ceil(month(TempDate2) / 3) as QuarterClosed,
'Q' & Ceil(Month(TempDate2) / 3) & '-' & Year(TempDate2) as QuarterYearClosed,
Date(monthstart(TempDate2),'MMM-YYYY') as MonthYearClosed,
InYearToDate(TempDate2, $(vToday), 0,10) * -1 AS CurYTDFlag,
InYearToDate(TempDate2, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal2
Order By TempDate2 ASC;
Drop Table TempCal2;
It seems to be a bug with the InYearToDate. If you use the Today() function instead of the variable it should work fine:
It seems to be a bug with the InYearToDate. If you use the Today() function instead of the variable it should work fine:
Hi,
maybe the calendar script from this thread helps:
Re: Re: Re: CUMULATIVE YEAR WITH MASTERCALENDAR
OrderCalendar:
LOAD Date as TCMSALESORDERDATE,
Week(Date) as TCMSALESORDERWeek,
Year(Date) as TCMSALESORDERYear,
Month(Date) as TCMSALESORDERMonth,
Day(Date) as TCMSALESORDERDay,
YeartoDate(Date)*-1 as TCMSALESORDERCurYTDFlag,
YeartoDate(Date,-1)*-1 as TCMSALESORDERLastYTDFlag,
InYear(Date, Monthstart(MaxDate),-1) as TCMSALESORDERRC12,
Date(monthstart(Date), 'MMM-YYYY') as TCMSALESORDERMonthYear,
'Q'&Ceil(Month(Date)/3) as TCMSALESORDERQuarter,
Week(WeekStart(Date)) & '-' & WeekYear(Date) as TCMSALESORDERWeekYear,
WeekDay(Date) as TCMSALESORDERWeekDay;
LOAD Date(MinDate+IterNo()-1) as Date,
MaxDate
While MinDate+IterNo()-1<=MaxDate;
LOAD Min(TCMSALESORDERDATE) as MinDate,
Max(TCMSALESORDERDATE) as MaxDate
Resident OrderHeader;
regards
Marco
Thanks, Daniel -- that worked.