Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zagzebski
Contributor

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: InYearToDate not working properly

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;

4 Replies
MVP
MVP

Re: InYearToDate not working properly

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

Re: InYearToDate not working properly

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
Contributor

Re: InYearToDate not working properly

Thanks - this helped alot for understanding.

zagzebski
Contributor

Re: InYearToDate not working properly

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.

Community Browser