topic Current Year and Last Year Flags in QlikView

Current Year and Last Year Flags

Wed, 31 Mar 2010 17:00:28 GMT

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,

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

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

I get Error in expression:')' expected

Much appreciate some guidance here,

Current Year and Last Year Flags

Wed, 31 Mar 2010 18:35:28 GMT

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

Current Year and Last Year Flags

Anonymous — Wed, 31 Mar 2010 20:43:59 GMT

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

Current Year and Last Year Flags

Thu, 01 Apr 2010 01:45:07 GMT

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;

Current Year and Last Year Flags

Thu, 01 Apr 2010 13:33:52 GMT

Thanks!