Current Year and Last Year Flags

[#1]

I am trying to put a CY and LY flag into my script but everything I try gives me errors.

I am loading essentially one giant ascii with all my records. [INVC DATE] is only one of these fields. When i try this...

[INVC DATE],

date(monthstart([INVC DATE]), 'MMM-YYYY') as RollingMonth,

date

(yearstart([INVC DATE]), 'YYYY') as RollingYear,

Month

(Date([INVC DATE])) as Month,

Weekday

([INVC DATE]) as WeekDay,

Day

([INVC DATE]) as Day,

Week

([INVC DATE]) as Week,

Week

([INVC DATE])&'-'&Year([INVC DATE]) as RollingWeek,

IF

(year([INVC DATE])=$(vCurrentYear),1,0) AS CY_flag,

IF

(year([INVC DATE])=($(vCurrentYear)-1),1,0) AS LY_flag,

I get Error in expression:')' expected

Much appreciate some guidance here,

[#2]

I have made a temporary fix by changing $VcurrentYear to '2010' but would really like to get this right for future years

[Anonymous · Accepted solution]

It should be:

if(year([INVC_DATE])=year(today()), 1, 0) as CY_flag
if(year([INVC_DATE])=(year(today())-1), 1, 0) as LY_flag

[#4]

Les -

There's also the InYear() function:

LET varToday = NUM(TODAY());
MasterCalendar:
TempDate AS OrderDate,
INYEAR(TempDate, $(varToday), 0) * -1 AS CurYearFlag,
INYEAR(TempDate, $v(varToday), -1) * -1 AS LastYearFlag
RESIDENT TempCalendar;

[#5]

Thanks!