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
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.
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?
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
Thank you very much @jasonblinn
I think I see where you and I differ.
When you say ....
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.
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
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
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.
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
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.