Level 2 Model Building Sprint 3 INV01 Module

Lkishko

Hi! 

 

I am struggling to complete the INV01 Module Line Item Formula Details. It seems like everything is working except for the "Confirmed PO Delivery" formula. Below is the formula I have and the result I get:

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

I've found similar questions in the community but no clear way to resolve this. 

Also, instead of the "-1" in the offset formula I would normally include "0" but this isn't the issue. 

 

Can someone help me resolve this? The goal is to have the 1 from "PO Submitted" be reflected in week 3 of "Confirmed PO Delivery"

 

Thanks in advance!

 

einas.ibrahim

@Lkishko 

That activity almost made me lose my head 🙄. There is an error in the instructions. I will be sending that to the Academy. But that's not why it drove me crazy. The instruction was pushing me to use a formula I didn't want to use because it's not right. Then later in the exercise, it turns out I was right.

 

Like @eriktwelvecg you would need to explore functions other than OFFSET, but that's later in the exercise. 

 

Now let's look at OFFSET formula you are using, so we touch on some concepts:

• First, in your formula, you are using -Shipping Time Weeks as the offset value. When the offset value is negative then the data is returned from prior periods.
• Second, you are using -1 as the fill value, which means if your OFFSET formula has an offset value outside the time range it will return the default value, -1 in your case.
  

 

Please review the OFFSET function on Anapedia.

 

Looking at the Confirmed PO Delivery in Week 1, the formula is basically saying:

• IF PO Submitted = 1, which it is. i.e TRUE
• Then return the value of the PO Submitted you will find in 2 prior periods.
  • Prior because you used -
  • 2 because the Shipping Time Weeks in this period is 2

• The formula now is looking at a value of the PO Submitted  2 periods before Week 1, that's outside the time range so it returns your the default value -1.
  

So,

1. You need to adjust your formula to read the value from the proper period.
2. You need to change your default value if you want to get a 0

 

An additional point that will not affect your results but it is rather a Best Practice to improve the performance. 

When using an IF statement, think of Early Exit. Meaning if you can get the formula to end after the THEN clause without going to the ELSE clause for the majority of the time then you are saving processing time. The way to achieve this is to start the IF function with the scenario you believe will happen more often.

In your case, what will happen more often? the PO Submitted = 1 or =0? Will we placing an order more often than using the default inventory values?

 

Let me know if you still have other questions. This is a "fun" exercise.

esvensson

@Lkishko I believe this is addressed in a subsequent step in L2 training, but I would explore the POST formula rather than OFFSET

Kanishq17

hi @Lkishko ,

 

offset function has the issue that it works on the target period basis 

so as per requirement for week 2 po submitted is 1 and shipping time is 2 weeks thereby confirmed PO delivery should have been 1 for week 4 but here it is 0 as offset basically brings values from n periods in the past (in case of minus) for each line item , so basically for each week , offset brings value of PO delivery from no. of periods before , for week 4 you see shipping time weeks as 3 thus it will post value of week 1 i.e 0 in this case . 

 

hence POST is an alternative here because it simply posts value n periods in the future

on using post it will simply shift value of week 2 to week 4 

 

Hope it clears