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

    Month calculation

    AndrewFish

      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
          LaRetta_1

          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
            raybaudi

            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
              AndrewFish

              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
                LaRetta_1

                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
                  raybaudi

                  "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 )