Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gjwdirect
New Contributor

Use of LastYTDFlag

I have a calendar using the script below

MinMax:

LOAD

Min(Date) as MinDate,

Max(Date) as MaxDate

RESIDENT Facts;



LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);



/*  Temporary Calendar



Generates a single table with one field containing

all existing dates between MinDate and MaxDate.



*/




TempCal:

LOAD

date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate) + 1;



DROP TABLE MinMax;



/* Master Calendar



Disconnected during the Date Island exercise by renaming TempDate as IslandDate



*/




MasterCalendar:

LOAD

//TempDate as IslandDate,

TempDate AS Date,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' &
ceil(month(TempDate) / 3) AS Quarter,

Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;



DROP TABLE TempCal;

Max date in my data is 19/11/2013 so I would like any transactions with a date after 19/12/2012 not to be flagged as LastYTDFlag 1 but 0

Dates up to 31/12/2012 are being flagged as 1 but they should be 0

The date filed in my data is loaded as

inception_date_tpi as Date,

Can anyone adice what I need to change in My Calendar for this to work.

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Use of LastYTDFlag

Create a variable vMaxDate = '19/11/2013'.  Then use the variable in place of vToday

Specifically:

inyeartodate(TempDate, $(vMaxDate), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vMaxDate), -1) * -1 AS LastYTDFlag

3 Replies
Employee
Employee

Re: Use of LastYTDFlag

Create a variable vMaxDate = '19/11/2013'.  Then use the variable in place of vToday

Specifically:

inyeartodate(TempDate, $(vMaxDate), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vMaxDate), -1) * -1 AS LastYTDFlag

MVP
MVP

Re: Use of LastYTDFlag

So your variable vMaxDate is 41597 (and vToday as well)?

inyeartodate( makedate(2012,12,19), 41597, -1)

is returning zero, so I can't reproduce your issue.

gjwdirect
New Contributor

Re: Use of LastYTDFlag

Thank you. That has given the result I would have expected.

Community Browser