CURYTDFLAG Not Working

[#1]

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;

[danieloberbilli · Accepted solution]

It seems to be a bug with the InYearToDate. If you use the Today() function instead of the variable it should work fine:

[MarcoWedel]

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

[#4]

Thanks, Daniel -- that worked.