Calculate time spent in each stage

[JonesBeach]

Hi I am trying to calculate the number of business days spent in each Manufacturing Stage.

I am attaching an Excel Data file. 

I have this code but it does not give me the out put in the corresponding row rather it starts from the next row. 

Join(Manufacturing)

LOAD

Product_ID,

'Manufacturing Stage',
'Beginning of stage date ',

If(Product_ID = Peek(Product_ID), NetWorkDays(Peek('Beginning of stage date'), ('Beginning of stage date'), $(vPublicHolidays)))-1 as [Time_Spent_in_each_stage]
Resident Manufacturing

Order By  Product_ID, Beginning of stage date ;

Could any one please help me get the result as shown in the Excel file attached below. Thanks so much in advance!

[rubenmarin]

Hi, I think you need to sort date descendant and switch the fields on NetWorkDays():

If(Product_ID = Peek(Product_ID), NetWorkDays([Beginnig of stage date], Peek([Beginnig of stage date]))-1) as [Time_Spent_in_each_stage]
Resident oManufacturingig
Order By Product_ID, [Beginnig of stage date] desc ;

Maybe you also need to check the conditions to substract one day, ie:

-If(WeekDay([Beginnig of stage date])<5,1,0)

[JonesBeach · Accepted solution]

Hi Ruben, 

This gave me the same result with the time spent in first stage starting from the row underneath it. 

I need the first stage time spent in the same row. 

Thanks for taking the time out to help!

[rubenmarin]

Hi, I see it shows on the same row:

[JonesBeach]

Thanks much Rubenmarin! I was missing the Desc part but by adding that in it worked perfectly. 

Regards,

Jabran