Discussion Board for collaboration on QlikView Scripting.
I have a calendar using the script below
MinMax:LOAD Min(Date) as MinDate, Max(Date) as MaxDateRESIDENT 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.
Go to Solution.
Create a variable vMaxDate = '19/11/2013'. Then use the variable in place of vToday
inyeartodate(TempDate, $(vMaxDate), 0) * -1 AS CurYTDFlag, inyeartodate(TempDate, $(vMaxDate), -1) * -1 AS 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.
Thank you. That has given the result I would have expected.