5 Replies Latest reply on Jun 1, 2011 8:11 AM by raybaudi

# Month calculation

### Title

Month calculation

### Post

I am trying to write a calculation that returns the months included between a start date and an end date.

e.g. Start date = 21/1/11. end date = 23/5/11

the returned result should = Jan¶Feb¶Mar¶Apr¶May or Jan, feb, Mar, Apr, May

If anyone knows how to achieve this I would love some help.

thank you kindly

• ###### 1. Re: Month calculation

A bit of information of your context and purpose would help us here.  It would also help to know your FM version.  Are you using FMP Advanced?

• ###### 2. Re: Month calculation

You could solve with a looping script, a recursive custom function, a repeating calculation field OR with an UNSTORED calculation field that calls itself.

I'll prospect you the calculation for the last one:

Let([
\$i = \$i + 1;
d = Date ( Month ( StartDate ) + \$i - 1  ; 1 ; Year ( StartDate ) )
];
Case(
d ≤ EndDate ; List ( Left ( Proper ( MonthName ( d ) ) ; 3 ) ; ThisCalculationField ) ;
Let ( \$i = "" ; "" )
)
)

• ###### 3. Re: Month calculation

To explain further....

I am using FM Pro advanced version 11

I have the following fields.

• Campaign start date (date field)
• Campaign end date (date field)
• Campaign duration (calculation field = end - start)
• Campaign Revenue (number field)
• Jan Revenue (number field that inserts an auto calc)
• Feb Revenue (number field that inserts an auto calc)
• March Revenue (number field that inserts an auto calc)....continue series i.e. a revenue field for each month of the year

I am wanting the users to be able to input the start date, the finish date and campaign revenue. From these inputs I want the revenue by month to calculate automatically. e.g. if the customer inputs the following data

Campaign start date: 21 feb 11

Campaign end date: 23 Jun 11

Campaign revenue: \$10,000

Campaign duration (calculation field) = 122 days

The results should populate as follows:

Jan revenue (number field): \$0

Feb revenue (number field): \$655.74 or (10,000/122)*8 days

Mar revenue (number field): \$2540.98 or (10,000/122)*31 days

Apr revenue (number field): \$2459.00 or (10,000/122)*30 days

May revenue (number field): \$2540.98 or (10,000/122)*31 days

Jun revenue (number field): \$1885.25 or (10,000/122)*23 days

Jul revenue (number field): \$0

I hope this makes more sense.

Thanking you!

• ###### 4. Re: Month calculation

rabaudi said, "with an UNSTORED calculation field that calls itself."

The calculation you presented is dangerous and undocumented behavior.

As quoted from Comment:

"For anyone reading this thread, you included, I'd recommend against forcing recursion by referencing a calculation field to itself. Although it might seem to work in some circumstances, it's not possible to predict how it will behave under other conditions. Unlike a custom function, there is no built-in protection against stack overflow and you might easily end up with the application crashing and your file being corrupted."

http://fmforums.com/forum/topic/78211-loop-through-text/page__p__365989__hl__calculation__fromsearch__1#entry365989

I myself have had issues with it as well.

• ###### 5. Re: Month calculation

"I myself have had issues with it as well."

It will be nice if you could make an example ( with recursion's number < 200 to be safe )