Level 2 Sprint 3 - Confirmed PO Delivery Formula; POST Function

MryP
MryP
edited November 2022 in Academy Discussions

Hello dear Community members, I have used the following formula for 'Confirmed PO Delivery':

IF PO Submitted > 0 THEN POST(PO Submitted, Shipping Time Weeks) ELSE 0

However, I am not getting the desired result. For example, I checked the box for 'Submit Purchase Order Request' for Week 1, so the 'PO Submitted' cell value changed to 1. But the value for 'Confirmed PO Delivery' shows 0 in Week 3 [where instead according to the POST formula as mentioned above, it should have posted 1 (from Week 1 'PO Submitted')], as the corresponding 'Shipping Time Weeks' for Week 1 is 2 (weeks, that is, the target is Week 3):

(Case 1)

Screenshot (10590).png

I tried to understand the issue, then, I noticed this (Case 2):

Screenshot (10591).png

In my IF THEN formula (from above), I had written that 'IF PO Submitted > 0' then (do the calculation) else return 0. So, when the system, I believe, read the cell value for 'PO Submitted' for Week 3 (which was not marked with a check, so it would have been 0) in Case 1, it would have gone to the ELSE part of the function to overwrite the initial value in 'Confirmed PO Delivery' (which would have been 1, as posted from Week 1, using the POST formula). To test this, I also checked the 'Submit Purchase Order Request' box for Week 3 (Case 2) and the 1 appeared in 'Confirmed PO Delivery' for Week 3. I think this is because the system read that 'PO Submitted' is 1, so it POSTs a value to another cell (as per THEN part of the formula) and leaves the current cell as it is, without overwriting it.

I would like to know how this issue can be overcome. I would like to know if there is any mistake in my approach. I think the ELSE part should have something that can keep the value as it is instead of 0, that is, to keep it as 1 if it was initially 1, but I am not sure how. Am I missing something obvious?

I think one obvious way would be to just write the formula for 'Confirmed PO Delivery' as:

POST(PO Submitted, Shipping Time Weeks)   

(This formula works well and seems logical, but might have performance issues, so I am not really sure).

 

Comments

  • einas.ibrahim
    einas.ibrahim
    edited November 2022

    Dear @MryP 

    You came across perhaps the most contentious topics for me - POST() in an IF
    Please read this thread where I raised the same issue.
    https://community.anaplan.com/t5/Anaplan-Platform/Known-Issues-with-using-POST-and-IF/m-p/71486#M14149


    As you will see, some of my colleagues believe that the POST is behaving as it should - even when in an IF statement. I happen to disagree.

    You can follow the discussion in the thread and hopefully find an explanation that satisfies your question.

    For now, I avoid using POST in an IF.

  • Saket2022
    Saket2022
    edited November 2022

    The below formula appears to take care of the issue:

    IF OFFSET(PO Submitted, -Shipping Time Weeks, 0) > 0 THEN OFFSET(PO Submitted, -Shipping Time Weeks, 0) ELSE 0

     

    Reason:

    Previous formula: IF PO Submitted > 0 THEN POST(PO Submitted, Shipping Time Weeks) ELSE 0

    Issue in the previous formula:

    For the IF formula to run the calculation mentioned in the IF TRUE part, it was checking the current month's PO submitted value. Whereas we want it to check the value mentioned in the PO Submitted column mentioned 2 weeks ago.

  • jsheldon2009
    jsheldon2009
    edited November 2022

    I was able to get it to work with POST(PO Submitted, Final Shipping Time Weeks)

  • SrikarAllena
    SrikarAllena
    edited November 2022

    My savior

  • TIWARBK
    TIWARBK

    This is the correct formula for Confirmed PO Delivery =POST(PO Submitted, Final Shipping Time Weeks)

    Confirmed Purchase Order Receipt = IF Confirmed PO Delivery > 0 THEN POST(Suggested Order Amount for the Month, Shipping Time Weeks) ELSE 0

Categories