POST function within IF THEN ELSE construct

dngingras

In a model I'm working on I need to use the post function.  When I put it by itself in the formula it works fine.  But since I only want to use it when a certain condition is met, I changed the formula to: if NOT lineitem then 0 else post(x,y).  With this syntax, all I get is 0s. Is this a known issue?

 

Thanks

jnoone

Hey - Can you confirm that your boolean line item is getting satisfied?  I have put together an example below and seems to operate as expected.  Is your setup similar? 

 

Thanks.

Jt

dngingras

the structure I have is similar except that the offset amount comes from a number formatted line item.

 

Thanks for the quick response!

dngingras

I figured out the issue.  The POST is working but I have think about my condition.  The way I have it now, if the condition is satisfied in week1 but not in week2, the formula in week2 override the posted value with 0.  Thanks for your help.

Srinivas_Gudi

hey ---when i working with post- if conditon ...not able tp get the proper results..

when i use only post function it give sproper results...

MaartenGijbels

Hi @Srinivas_Gudi ,

 

It could be related to a lot of things, but in principle it should work.

 

Have you considered breaking up the formula in multiple parts? By using a separate line item for the value if true, value if false and the actual IF-condition it typically helps you to structure.

 

All the best,

Maarten.

Srinivas_Gudi

Have Taken 3 Line Items;

Volume and Post Function are  Number Format, Boolean Is Boolean Format.

 

Post Function : IF Boolean THEN POST(Volume,2) ELSE 0

 

                                JAN 20  FEB 20   MAR 20  APR 20    MAY20   JUN20

Volume                :      10          12         14            16              18           20

Boolean               :                                  T            T

Post Function.     :                                 10          12

 

Why the results are coming like this. 

 

Whenever Boolean Condition Is true , post the volume line item values into further cells. it means third column is true so  want to post volume results in may20.

MaartenGijbels

@Srinivas_Gudi ,

 

Are you referencing the line item with the post function or the post function itself? 

 

 

Srinivas_Gudi

Am referencing the volume line item in Post function 

 

dgingras

If you want to see 14 in May20, try LAG(Volume,2,0).  POST is working correctly.

Srinivas_Gudi

i tried it ....not able to get the results.

 

dgingras

The POST, LAG, OFFSET function all take a value from a prior or future period and put it in the current period. So if you want to put a value in June based on a condition set in April, you need to test that condition in the prior period. I believe this will give you what you want:

IF PREVIOUS(PREVIOUS(Boolean)) THEN POST(Volume, 2, 0) ELSE 0

dgingras

Here is the test I did:

Isn't this what you are looking for?

Srinivas_Gudi

Thanks for the information... 

But when i working with POST Function without if condition it gives proper reuslts.

it will post the results in Prior to the cells as well as previous cells.

 

Post Function  :  POST(Volume,2)

                           Jan20   Feb20   Mar20  Apr 20  May 20

Volume                  10        20         30        40         50

Post  Function                                10       20         30  

  

dgingras

The POST function is not pushing the value from Jan to Mar, it is pulling the value from Jan to Mar. The reason there are no values for Jan and Feb is that the source period is outside the date range.

Srinivas_Gudi

But Actually....if the condition is true in APR 20 means ,i want  post those Line Item  values inJUN20.

 

Srinivas_Gudi

In that Above Example ...We can post  the present cell value to the prior cells....

POST Function will post the present value to the prior/previous to the cells....

dgingras

Try this, it should clarify how the POST function works.

 

IF Boolean THEN POST(Volume, 2, 0) ELSE POST(Volume, -1, 0)

 

Hope this helps.

einas.ibrahim

Hi Everyone,
@dgingras @Srinivas_Gudi 

I'd like to correct some of the information I saw in this thread, even if you have figured out the issue, future contributors who read this will not be confused.
"The POST, LAG, OFFSET function all take a value from a prior or future period and put it in the current period"

This is not accurate.

 

POST differs from LAG and OFFSET because it actually takes the value from the current period and put it in a future or prior period.

I consider POST as a PUSH while OFFSET and LAG  are a PULL

I generally find the technical explanation of Anapedia confusing - until they provide an example. In the case of POST, I believe the first line of the description is wrong, best-case scenario it is very misleading
POST (Anapedia)
Returns the value in a line item from a future or previous period based on a positive or negative offset value.

It should be
Returns the value in a line item TO a future or previous period based on a positive or negative offset value

I also believe that the formula 

dgingras

Thanks Elinas for correcting my error, after I posted it I realized that was wrong but forgot to correct.  I couldn't agree more with you that the documentation is confusing.  To add to the confusion, when another line item is updated and it causes the re-evaluation of all line items, it makes it appear, to me at least, that the POST works differently than it actually does.

duncan_pearson

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.