Monday 13 May 2013

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

   return rval


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

No comments:

Post a Comment

Note: only a member of this blog may post a comment.