AnsweredAssumed Answered

Date Calculation - Previous Months

Question asked by tomoy on Mar 25, 2013
Latest reply on Mar 26, 2013 by tomoy


Date Calculation - Previous Months


Hi - I'm building a tool in FMP12 to help better present our ERP's SQL data which creates running sales buckets for the current month, and the previous 12 months.  It looks something like CURR, MON-1, MON-2 etc. to MON-12.

So I'm placing calculated fields next to these as translated field tags in the report.  They'll need to be accurate no matter which day the file is opened and viewed.  Also, I'm shooting for the format YYYY-MM.  Current month is easy:

Year ( Get ( CurrentDate ) ) & "-" & Right ("0" & Month ( Get ( CurrentDate ) ); 2)

Currently this should look like 2013-03 instead of CURR and 2013-02 instead of MON-1.

However I clearly run into issues as soon as subtracting months means I'm looking at last year.  Does FMP have a way to handle dates where subtracting months is smart about subtracting years?  If not, I guess I'll have to do a Year calculation with If's for when the subtraction would equal last year.  Then a Month calculation for when the subtraction would go negative and wrap back to last year.

Maybe I'll remove the hyphen and just work with YYYYMM as an integer and calculate If's or Case where my months are in the 99-88 zone.

Any pointers/experience much appreciated!