AnsweredAssumed Answered

Calculating Dates for the last 12 or 15 months

Question asked by ndveitch on Jul 29, 2016
Latest reply on Jul 29, 2016 by beverly

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?