extrapolation.. urgent

[n1ef5ng1]

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

[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...

)

[#3]

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