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,




            • 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:




              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?