topic Re: InYearToDate not working properly in QlikView

InYearToDate not working properly

zagzebski — Fri, 12 Sep 2014 16:51:38 GMT

I can't get my Inyeartodate function to work properly. When I do the following the "YTD" flag below is givinh me date from the year 1906!

vYTD = date(Floor(MonthEnd(today(),-1)),'YYYY-MM-DD');

D_CALENDAR_DATE_PRE1:
LOAD
date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date
FROM
$(vInputFile1)(qvd);

D_CALENDAR_DATE:
NoConcatenate
LOAD *,
If(InYearToDate(Standard_Date,$(vYTD),1),1,0) as YTD
Resident D_CALENDAR_DATE_PRE1;

Thanks in advance,

Steve

Re: InYearToDate not working properly

maxgro — Fri, 12 Sep 2014 18:37:06 GMT

DIRECTORY;

// generate a test calendar and store

Calendar:

load

date(makedate(2013,1) + rowno()) as CALENDAR_DT

AutoGenerate 1000;

store Calendar into Calendar.qvd (qvd);

DROP Table Calendar;

// your script starts here

let vYTD = date(Floor(MonthEnd(today()),-1),'YYYY-MM-DD');

D_CALENDAR_DATE_PRE1:

LOAD

date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date

FROM

Calendar.qvd (qvd);

D_CALENDAR_DATE:

NoConcatenate

LOAD *,

If(InYearToDate(Standard_Date,'$(vYTD)',-1),1,0) as YTD

Resident D_CALENDAR_DATE_PRE1;

Re: InYearToDate not working properly

Fri, 12 Sep 2014 18:45:32 GMT

let vYTD = date#(Floor(MonthEnd(today(),-1)),'YYYY-MM-DD');

//---- Also an option

//let vYTD2 = Date#(MonthsStart(1,Today(),0)-1);

D_CALENDAR_DATE_PRE1:

LOAD

date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date,

InYearToDate(Date(CALENDAR_DT),$(vYTD),0)*-1 as YTD

FROM $(vInputFile1)(qvd);

With this solution, the InYearToDate returns -1 (true) or 0 (false) if CALENDAR_DT is between 1/1/<Current Year> and the last day of the previous month.

You don't need to load two tables for the same data. The last 1 in InYearToDate(Standard_Date,$(vYTD),1) is not a valid number, has to be between 2 and 12 or 0.

ex.

vYTD = 9/12/2014

CALENDAR_DT= 12/1/2013

YTD = 0

vYTD = 9/12/2014

CALENDAR_DT= 1/5/2014

YTD = 1

vYTD = 9/12/2014

CALENDAR_DT= 10/5/2014

YTD = 0

Hope this helps!

Re: InYearToDate not working properly

zagzebski — Fri, 12 Sep 2014 19:05:19 GMT

Thanks - this helped alot for understanding.

Re: InYearToDate not working properly

zagzebski — Fri, 12 Sep 2014 19:07:48 GMT

Thanks. Looks like I was misssing the apostrophe's in '$(vYTD)' . But to be honest I am not sure why they are needed from a coding standpoint.