Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
)
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