3 Replies Latest reply on Jul 29, 2016 6:17 AM by beverly

    Calculating Dates for the last 12 or 15 months

    ndveitch

      Hi there,

       

      Is there an easy way to get a value from a date for the previous year to work in calculations? I need to display a report based on the last 12, 15, or 24 months based off a user provided date. In my script I prompt the user for the date they want the report to show (zg_Date - global field), then I go look for the records from zg_Date less either 12, 15 or 24 months to zg_Date. Once the required records have been found, I import them into the Report table where I am trying to use calculations to sort the values into various fields to use in my report.

       

      In a nutshell, getting the values for the current month are easy, its getting the values for the previous months which I think i might be going down a rabbit hole. If for example, the date for which the report is up until is July, so zg_Date = 29/07/2016, then for the previous months going back to January are easy, just look for month(date) - 1 for June, or - 2 for May and so on. The problem is that when I reach December of the previous years i am running into issues, because Month(Date) - 7 would give me 0 and not 12. I am trying to build this so that it doesnt matter what date the user puts into zg_Date, the system should workout the last however many months automatically.  

       

      I am trying to get calculations working for each of the months in my report. For example, for CurrentMonth copy the vaule where month(date) = month(zg_date), then MonthLess1 would the value if month(date) = month(zg_date) - 1, MonthLess2 would be the value where month(date) = month(zg_date) - 2, and so on. I was thinking about saying zg_date - 30 or 60 or 90 days, but sometimes i have found in the past that causes more issues than is worth the effort.

       

      Is this a simple thing, or am I going about this the wrong way?