topic Re: Prior YTD in QlikView

Prior YTD

carlcimino — Thu, 01 Mar 2018 20:14:39 GMT

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);

Re: Prior YTD

sunny_talwar — Thu, 01 Mar 2018 20:17:33 GMT

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,

Re: Prior YTD

carlcimino — Thu, 01 Mar 2018 20:19:53 GMT

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.

Re: Prior YTD

sunny_talwar — Thu, 01 Mar 2018 20:25:45 GMT

May be this

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

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

Re: Prior YTD

carlcimino — Thu, 01 Mar 2018 20:39:56 GMT

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