POST function within IF THEN ELSE construct

Certified Master Anaplanner

Re: POST function within IF THEN ELSE construct

It's actually even more complicated than that.

Consider this formula :-

  • 'Immediate costs' + POST( 'deferred costs' , 'months to defer' )

It has three parts

  1. 'Immediate costs' - this is outside the POST function
  2. 'deferred costs' - this is the amount to post
  3. 'months to defer' - this is the number of months offset

For a given period - e.g. May - the expression 'Immediate costs' is evaluated for May.

Then 'months to defer' is evaluated for every month, and for each month in which the months offset points to May then 'deferred costs' is evaluated in that month and added to the result for May.

As a consequence May can contain values from many other periods. The underlying engine, when it is calculating the result for May, goes to all the other periods and pulls their values into the result. The difference is in where the "offset" expression is evaluated. For OFFSET, LEAD, and LAG the offset number is calculated only in the target period and only one value is pulled. For post it is evaluated in all periods for every target period. This is why it is important for the offset expression to be as simple as possible, either a constant or a reference to another line-item. If you have e.g. a 5 year weekly timescale (261 periods) then the offset expression is evaluated 68121 (261 x 261) times.

 

If the "amount to post" and the "offset" are complex sub-expressions (e.g. IF THEN ELSE) then it is important to understand that the whole sub-expression is evaluated in the source periods. This means that if you want to do a conditional POST then the condition must be within the POST() function.