AddMonths function in QlikView Expressor

QlikView Expressor comes with a programming language, called Lua, to which they have added their own objects and functions.  One of these is the datetime object which has several functions to allow the manipulation and parsing of dates - something that will be done in an ETL environment quite frequently.

One of these function is adjust - used to add or subtract different time periods from a date.  When looking at the documentation, there was an interesting, to me anyway, omission in the type of period that you can adjust a date by.  It does include seconds, minutes, hours, days, years and centuries, but no months!

Now, month arithmetic should be straightforward but there are complications when you have dates after the 28th of any month - what happens if you subtract 3 months from the 29th May 2013?  If you don't handle it correctly, you will get an invalid date.

In this Lua function, I handle that eventuality by using a Lua pcall function to test if the returned date is valid and, if not, subtracting a day until it is.


function AddMonths(dt, months)

    -- Parse the month, year and day of the date
    mn = datetime.moment(dt, "m")
    yr = datetime.moment(dt, "y")
    dy = datetime.moment(dt, "d")
    
    -- Add the number of months to the month
    mn = mn + months

    -- If this resulted in a month value over 12,
    -- then we increment the number of years and
    -- decrease the month value by 12 until it is
    -- less than 12 again
    while mn > 12 do
       mn = mn - 12
       yr = yr + 1
    end
    
    -- Similarly, if the calculation leaves us
    -- less than zero, we need to subtract years
    while mn < 1 do
       mn = mn + 12
       yr = yr - 1
    end

    -- If the day value is over 28, the convert might not parse so we drop the number
    -- of days until it does (e.g. starting with 31st May, subtract 3 months gives
    -- Feb - 31st Feb is an error so we need to go back to 28th Feb)
    if dy > 28 then
       -- Wrap the call in a pcall() to test if it works - if it fails, we subtract a day
       while not pcall(function() string.datetime(mn .. "/" .. dy .. "/" .. yr, 'M*/D*/YYYY') end) do
          dy = dy -1
       end
       rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
    else
       rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
    end

    return (rval)

end

So, you would call it something like this:


bd = string.datetime("20130529", "YYYYMMDD")

print(datetime.string(AddMonths(bd, -3), "CCYY-MM-DD"))


This may not be be most efficient way to do this, but it is a useful demonstration of using pcall and doing month arithmetic.

---

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