2 Replies Latest reply: Feb 5, 2014 8:29 AM by Erica Whalley RSS

    extrapolation.. urgent

    ben lim

      Hi I need the code where the current month will extrapolate to forecase on the estimated volume for the current month.

      The formula is basically the amount of volume / the number of days * the number of days in the month

       

      say today is 10th and the volume is 2000 for simplicity,

       

      the formula will be 2000/10 * 30 = 6000 volume predicted for the month of Feb 2014..

       

      the code that I have is

       

      =

      If ( MonthEnd ( MAX( Date ) ) <> today()-4 and MonthEnd( MAX ( Date ) ) = MonthEnd(today()-4 )
      ,
      sum({<Date ={'<=$(=Date(today()-4))'}>} TEU)/ Day( today()-4 ) * Day(MonthEnd( MAX ( Date ) ))
      ,
      sum(TEU) )

      I added today() - 4 because our data only becomes  official on the previous  three day. this  is because it take days for us to finalize volume of individual day.

       

      The problem that I have is, right now, on the third of feb. it actually extrapolate the month of Jan as  today()-4 is actually the last day of Jan. So it actually extrapolated on the month of Jan when it is not the current month.

       

      is there any way i can factor in the rule of this. thank you

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Re: extrapolation.. urgent
          Carlos Reyes

          I'm sorry if I don't get the idea right... but perhaps you should use the next if condition:

           

          IF ( MonthName( MAX( Date ) ) = MonthName ( Today () )  and Day ( Max (Date) ) >= 4,

          Sum...,

          Sum...

          )

            • Re: extrapolation.. urgent

              Hi, am I right in thinking you want the day-4days as a reference date, unless it is before the 4th of the month then use today?

               

              A simpler expression to try for the date:

               

              today()+(day(today())>4)*4

               

              will return today if the day is <= than the 4th, or 4 days ago if it is after.

               

              So the 5th would return 1st feb.

               

              Put this into a variable and use that as the date to refer from in the expression

               

              Is this what you need?

               

              Erica