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

# Calculating Dates for the last 12 or 15 months

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.

• ###### 1. Re: Calculating Dates for the last 12 or 15 months

FM is actually good at this and it is very easy to put together.

Try this in the data viewer, which will give you the date 16 months ago.  You can do the same "math" for days and years, just subtract how many you want from the relevant part

let(

[

_now = get(CurrentDate) ;

_date = Date ( month( _now ) - 16 ; day ( _now ) ; year ( _now ) )

];

_date

)

1 of 1 people found this helpful
• ###### 2. Re: Calculating Dates for the last 12 or 15 months

Thank you so much. I can't believe it was that simple.

• ###### 3. Re: Calculating Dates for the last 12 or 15 months

Yep! FileMaker dates do math!!