L2 Model Building - Sprint 3 - confirmed PO delivery

5M5X

I am stuck with building the formula for "confirmed PO delivery"

In case line item "PO submitted" is 1 for week x, it should show 1 in line item "confirmed PO delivery" for week x + offset. The offset number is depending on the shipping time shown in week 1.

I used formula: IF PO Submitted = 1 THEN OFFSET(PO Submitted, -Final Shipping Time Weeks, 0) ELSE 0

But this is showing 0 in all weeks

Can someone help me with this formula? Thanks !!!!

Prajjwal88

Hi @5M5X ,

For the Confirmed PO Delivery, you simply need to shift the value based on the Final Shipping Time Weeks. The correct formula would be:

POST(PO Submitted, Final Shipping Time Weeks)

There's no need to add the IF THEN ELSE clause since the PO Submitted line item already contains values of either 1 or 0. The POST function will automatically shift the 1 as per the shipping time, while the remaining values will be 0.

Hope this Helps.

5M5X

yes that helps a lot!!!!

Dikshant

More details on these functions, OFFSET function fetches the values from past or future period whereas POST function pushed the values to past or future period. In this case you need to push the PO Submitted to future weeks defined in Final Sipping Time Weeks line item. Hence the formula POST(PO Submitted, Final Shipping Time Weeks)