YearToDate function in Lua for QlikView Expressor

This is a good function to have around and one that is useful to demonstrate being able to have optional parameters in functions.  We just need to check that a parameter is not nil.  If it is, we can set a default value.

Here is the code:


function inyeartodate(vdate, vbasedate, vshift)

   -- Accept a date and compare it to the base date
   -- If it is in the same year up to and including the
   -- basedate, then true.  The shift changes the
   -- year to compare.  E.g. -1 is YTD last year.

   rval = 0

   -- The parameters may not have been passed so establish defaults
   if vdate == nil or not is.datetime(vdate) then vdate = datetime.timestamp() end
   if vbasedate == nil or not is.datetime(vbasedate) then vbasedate = datetime.timestamp() end
   if vshift == nil or not is.number(vshift) then vshift = 0 end
   
   -- In case the shift is not an integer, floor it
   if not is.integer(vshift) then vshift = math.floor(vshift) end

   -- If the vshift is not zero, we ajust the base date
   if vshift ~= 0 then vbasedate = datetime.adjust(vbasedate, vshift, "y", true) end

   -- If the vdate is less than the base date, 
   -- and they are in the same year, then
   -- the result is true - return -1
   if vdate <= vbasedate and datetime.moment(vdate, "y") == datetime.moment(vbasedate, "y") then
      rval = -1
   end

   return rval

end


And you might call it like:


d = string.datetime("20120514", "CCYYMMDD")
b = string.datetime("20130531", "CCYYMMDD")

print (inyeartodate(d))
print (inyeartodate(d, b, -1))


Another interesting thing to note is the "true" parameter to the datetime.adjust function.  This causes the function to use 365 days in the calculation rather than 365.25.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond