Known Issues with using POST and IF??

[einas.ibrahim]

Hello everyone,
@Misbah @JaredDolich @rob_marshall 

If you know there is an issue with using POST with IF then please let us know, if you don't please read on.

Context: We would like to move a value from the current period in a line item to another line item x periods ahead under a certain condition.

IF Condition is satisfied 
THEN POST(Source, x)

ELSE 99

If we don't use the condition, the POST function produces the results as expected (see below)

If we add the IF condition based on the condition line item, POST doesn't perform as expected. It doesn't "move" the Source Value  and the formula resolves to 0 in the same period (see below)

As a side note, I tried to use a numeric condition and the results were the same.

Finally, when I hard code TRUE as the condition, the POST function works (see below)

Does anyone have an explanation for this behavior? even if the explanation is this is a bug.

Thanks

[jasonblinn]

If you drill down in May it will not show the condition is met and will return 99.

In Feb the Condition is met, but the time does not go back that far, so you are getting zero. 

I believe the formula is working as it is written, and you need additional logic to make it work as you want it to. 

I can try to mock up an example if that would be helpful. 

[einas.ibrahim]

Hi @jasonblinn 
Thank you

Why are you saying in Feb "time does not go back that far"? We should not be looking back, we should be looking forward 3 periods.
However, I think you are onto something because when I checked Condition in May, the Source value (100) appeared in May

Unlike OFFSET/LAG/LEAD, for example, this is how I "translate" the POST formula -
IF Condition THEN POST(Source Value, Distance in Periods) ELSE 99 In the FEB Time Period

IF Condition[FEB] is TRUE
THEN POST(Source Value[FEB], 3 Periods in the future [May])
ELSE return 99

This is how I see the difference between OFFSET (pull) and POST (push)

So I don't think the formula is working as it is written (Unless my understanding is off).

Also, regarding if the condition is met or not, I added the line Item Condition Value and it shows TRUE for February, so what gives?

[jasonblinn]

@einas.ibrahim 

For Feb: 

IF Condition[FEB] THEN POST(Source Value, Distance In Periods)[Nothing that meets this critera] ELSE 99

Since there is nothing in the Post logic that is telling it to land in FEB then your formula is showing 0

For May: 

IF Condition[MAY] THEN POST(Source Value, Distance In Periods)[FEB] ELSE 99

Since in May the condition is not met then it is jumping to the 'ELSE 99' Argument of the formula.

Using this formula, you would need to check the condition in May:

IF Condition[MAY] THEN POST(Source Value, Distance In Periods) ELSE 99

The condition portion of the formula does not get considered in the post.

If you could have them check the condition in the month that it should be displayed in, that would be the easiest I believe, otherwise, you need to likely do some staging move this data in the way you are wanting. 

I hope this helps! I am happy to try to mock this up over the next day or so if that would be helpful using the different staging lines.

Jason

[einas.ibrahim]

Thank you very much @jasonblinn 

I think I see where you and I differ.

When you say ....

1. IF Condition[FEB] THEN POST(Source Value, Distance In Periods)[Nothing that meets this critera] ELSE 99
   Well, the criteria is met. Condition[Feb] is TRUE
   So I don't understand this point.
2. "Since there is nothing in the Post logic that is telling it to land in FEB then your formula is showing 0"
   The POST logic should be telling it to land in MAY, not in FEB. Why do you say FEB? I'm in FEB (starting point) asking that the value lands in MAY.
3. If you could have them check the condition in the month that it should be displayed in, that would be the easiest I believe, otherwise, you need to likely do some staging move this data in the way you are wanting. 
   If we wanted to check the condition in MAY, we would use OFFSET/LAG, but since -according to the requirement- when we are in MAY I don't know how many periods we should go back, we use POST in the source because in FEB I know exactly how many periods I need to move.

Am I not correct when saying the POST works differently than OFFSET/LAG?

I think what is causing the confusion is that the POST on its own is working fine.
The POST with a hardcoded TRUE also works, It is only when I'm asking that the condition be checked dynamically.

[jasonblinn]

@einas.ibrahim 

I think we are saying the same thing, but the issue is with how the IF Condition part is working. Since that is in no way tied to the Post portion of the formula it is looking at the month in which it falls (aka its ITEM(TIME)). The Condition for FEB impacts the Target Value in FEB. The Condition for MAY impacts the Target value for MAY. the Condition for FEB does NOT impact the Target value for MAY.

1. Since you have condition in FEB checked, then the Target value for FEB is 0 since there is nothing that gets posted into the month of FEB. The Condition for FEB only impacts the Target Value for the Month of FEB, so the formula is working as it is written.

2. The post logic is telling it to land in MAY but it will only land there if the Condition is checked in MAY otherwise it will show 99. Since you are seeing 99, then the formula is working as it is written.

3. Correct, This is where you would need to stage this. You will probably want staging lines that say: IF Condition then Source Value ELSE 0... And one for the Distance in periods, and then you can base your POST formula on those staging line items.

The POST is working properly, but the formula with how it is written is not allowing it to work the way you want it to. 

Jason

[Misbah]

@einas.ibrahim 

Not that I am aware of but it should work. I didn't find any constraints as well on Anapedia that  says it can not work along with other expression. Will create an example and check

[einas.ibrahim]

Hey @Misbah 

I didn't find any constraint either, that's why I'm thinking either something is wrong or I don't understand how the formula should work. either way, I need answers 😁

Please create your own example and let's circle back.

[Misbah]

@einas.ibrahim 

Firs of all thanks for discovering this and sharing this with us

I am leaning towads @jasonblinn explantion. Here boolean is supposed to be checked against each month and if it is not checked for a specific month and at the same time POST function is supposed to push values to that particular month, numbers are not going to get populated in that month.

Scenario 1 Below:

Jan is checked but April is not checked in Boolean condition

Number of periods (3) expects numbers to be pushed to April

Since April is not checked POST throws ELSE output which is zero in this case

Scenario 2 Below:

Jan is checked and April is checked too in Boolean condition

Number of periods (3) expects numbers to be pushed to April

Since April is checked POST pushes the values (1000 )from Jan to April

Scenario 3 Below:

Jan is not checked but April is checked in Boolean condition

Number of periods (3) expects numbers to be pushed to April

Since April is checked POST throws ELSE output

Workaround:  We have to split the IF statement in a diff line item 

Bottom line: There should be constraints in the formula that we can't use such IF statements with POST

[einas.ibrahim]

Thanks @Misbah 

In my opinion, to check the boolean of the target month doesn't make sense, and wouldn't fulfill the "business case/requirement". That boolean represents a certain condition,  for example, initiate a money transfer in Feb so it would arrive at the other account in May - based on the number of periods.
If we check May as well then we are saying initiate a transfer in May too. that doesn't agree with the requirement.

Also, if you think of this as a User Story- as a user I want to be able to initiate a money transfer in a certain period and based on the bank or the transfer method, account for that money in the period when it should arrive.
As the user, I should expect to check the boolean in say Feb only and have everything else automatically calculated. 

So, my conclusion - unless someone convinces me otherwise -  is that this is a bug or best case scenario a constraint should be added to the POST function technical documentation stating that it can't be used with IF.