Prior YTD

[carlcimino]

Hello, Trying to add some flags to my calendar and am having trouble getting the PYTD flag working.  The script below is what I have but it returns all N.  Any suggestions?  Thanks in advance.

CommonCalendar:

Load

  ACCOUNTING_YEAR,

     left(ACCOUNTING_QUARTER,2) as ACCOUNTING_QUARTER,

     ACCOUNTING_MONTH,

     MONTH_NUMBER,

     DAY_OF_WEEK,

     ACCOUNTING_DATE,

     ACCOUNTING_MONTH_YEAR,

     ACCOUNTING_DATE as Date,

     if(ACCOUNTING_YEAR=year(Today()),'Y','N') as CY_FLAG,

     if(ACCOUNTING_YEAR=(year(Today())-1),'Y','N') as PY_FLAG,

     if(MONTH_NUMBER=month(Today()),'Y','N') as CM_FLAG,

     if(ACCOUNTING_DATE>Today(),'Y','N') as FUTURE_FLAG,

     if(ACCOUNTING_DATE<=Today() and ACCOUNTING_YEAR=year(Today()),'Y','N') as YTD_FLAG,

  if((ACCOUNTING_YEAR=year(Today()-1) and ACCOUNTING_DATE<=addmonths(today(),-12)),'Y','N') as PYTD_FLAG

FROM [lib://Daily_Quote_QVDs/AcctCal.qvd](qvd);

[sunny_talwar]

How is ACCOUNTING_YEAR created in the QVD load before this QVD? Is it possible that it is a yearstart date by any chance? May be try this

If(Year(ACCOUNTING_YEAR)=(Year(Today())-1), 'Y', 'N') as PY_FLAG,

[carlcimino]

Hi Sunny, Thanks for the reply; I have the PY flag working.  It is the PYTD flag that I can't get working.  I want to compare where I am through this year to the same point last year.  ACCOUNTING_YEAR is a hard value from an oracle table.

[sunny_talwar · Accepted solution]

May be this

If(ACCOUNTING_YEAR = Year(Today())-1 and

   ACCOUNTING_DATE <= AddMonths(Today(), -12), 'Y', 'N') as PYTD_FLAG

[carlcimino]

That did it. One of these days I'll get good at where the parenthesis are supposed to go!  Thank you so much!