Level 2 Sprint 3 INV01


Hi, Im working on the formulas for Level 2 Sprint 3 and a couple of things dont make sense to me:


1. The formula for Confirmed PO Delivery give an example "For example, if a PO Submitted has a value in Week 1 and the Shipping Time Weeks has a value of 2, there would be a value of 1 in the Confirmed Purchase Order Receipt line item in Week 3."  Using the Offset formula, this only happens if the offset value is set to a negative amount (-Shipping Time Weeks); if I use a positive amount (Shipping Time Weeks), the offset works in the reverse direction where the Week 3 PO Submitted is Confirmed PO Delivery in Week 1.  This also goes against the verbiage on the Offset formula in Anapedia -- I must be doing something wrong but not sure..


2. The last line of the instructions indicate the formula of Confirmed Purchase Order Receipt = If Confirmed PO Delivery >0 Then Offset (Suggested Order Amount for the Month - Shipping Time Weeks) Else 0.  There should not be a minus sign in the Offset formula, it should be a comma.


Attached screenshots of the items above (#1 in red, #2 in blue in Level 2 INV 01 Formulas file) as well as my module (Level 2 INV 01 Module file).


3. BTW, I couldn't label this as "Level 2" so had to choose "Other" - not sure if there is a way to create a new label...this is my first time posting..


  • Hi @niti 
    What offset does is return the source value from a specific number of periods before or after the current period. 

    A purchase order submitted in week 1 should reach in week 3(due to 2 weeks of shipping time), what you really want to see is week 1's value in week 3. So if you think of it, for week 3, you want value from week 1 i.e. you want to offset the value '-Shipping Time'. 

    If you make it a positive 2, you are asking to pull value in week 3 from week 5. 


    Let me know if this makes sense.


    For the item marked in blue, I think you are right. It should be , 

  • niti

    Hi @ankit_cheeni Thanks for the reply!


    Regarding #1, I think I was confused about offsetting the source vs target line item -- so Offsetting Week 5 'Confirmed PO Delivery' to Week 3 'PO Submittted' (and not Offsetting Week 3 'PO Submitted' Week 3 to Week 1 'Confirmed PO Delivery') if that makes sense!


    Regarding #2, hopefully the Academy updates the verbiage.  TBH, the formula pop-up for those 4 line items could be made more clear.


    Thanks again!

  • Initially, I was using 'OFFSET(PO Submitted, -Shipping Time Weeks, 0)' as my formula for the Confirmed PO Delivery line item. This worked well until I got further along in the training and was required to add the ability to override shipping method to the module. I added the necessary line items and updated my formula to 'OFFSET(PO Submitted, -Final Shipping Time Weeks, 0),' but after doing this, the Confirmed PO Delivery and Confirm Purchase Order Receipt line items return 0 for all weeks. What changes do I need to make to my formula to offset based on the Finial Shipping Time Weeks line item?

  • Hi All,


    I'm facing the same problem but I still couldn't get the solution.


    I understand the logic of OFFSET function.

    But I'm not following what's the correct way to use it so my value from Week 1 appears on Week3


    I made some tryings


    IF PO Submitted > 0 THEN OFFSET(Suggested Order Amount for the Month, -Shipping Time Weeks, -1) ELSE 0


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


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


    But none of them seems to make sense for what I need.


    Thanks already.



  • @sara.sa 


    For which line item are you attempting to use the formulae you list in your post?


    This block of 4 line items are doing 4 main things:


    1. Submit Purchase Order Request is a simple boolean input that drives what happens below on the next 3 lines

    2. PO Submitted is just converting the boolean input above into number format for use in the next line item (pity that boolean algebra isn't possible in Anaplan like it is in e.g., Python; this line item wouldn't be necessary)

    3. Confirmed PO Delivery is really about offsetting the checkbox entry in line 1 by a number of weeks equal to the shipping time. The first three line items are a bit more intimately connected than the fourth is because they all revolve around that straight boolean logic.

    4. Confirmed Purchase Order Receipt is about using that offset boolean value to set when a shipment hits the inventory (as requested, but appropriately lagged by the shipping time as done in line 3), but with a different, more useful value being returned.


    Does that make sense?



  • Hi,


    Thanks for your answer.


    I'm trying to set the formulas for Line Items:

    Confirmed PO Delivery

    Confirm Purchase Order Receipt 


    - Line Item 'PO Submitted' is a simple formula to the boolean line item 'Submit Purchase Order Request':

    IF Submit Purchase Order Request THEN 1 ELSE 0

    Thats ok for me


    -Line Item 'Confirmed PO Delivery'

    This one is my struggle because I wanna check if Line Item 'PO Submitted' is > 0 and use the number of weeks to OFFSET it.

    Then my formula would be:

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


    But this doens't look right and is not working as I want to.



     -Confirm Purchase Order Receipt:

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


    I have some doubts here because I want to see the value of line item 'Suggested Order Amount for the Month' from week 1 on line item 'Confirm Purchase Order Receipt' at week 3, right? And with those formulas I have the value from week 3 in week 1, it's the opposite


    Thanks again.


  • @sara.sa 


    I have gotten past this exercise and feel like I've been bitten very hard for how I implemented my solution later on down the line, so please take what you're about to read with a grain of salt (though what I did here did pass muster per the check-up activity coming up ahead).


    A couple of hints for your last couple of functions:


    If your values are moving in the wrong direction, try changing the sign of the value governing how far it moves.  One of your arguments may be compared closely to a time displacement vector, and as such it has both magnitude and direction.


    Also, you may not need all the conditionals you're applying.  You might try dropping them in turn to see if you hit paydirt.