Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

InYearToDate not working properly


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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

4 Replies
maxgro
MVP
MVP

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;

Not applicable

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!

zagzebski
Creator
Creator
Author

Thanks - this helped alot for understanding.

zagzebski
Creator
Creator
Author

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.