How to find Sum of Wait Time time for manufacturing

[Austin Fox]

For Example:

 

 

 

Item

InDate

OutDate

Area

 

 

 

 

 

1

 

1/1/2020

1/3/2020

 

A

 

 

 

2

1/2/2020

Null

A

 

 

3

1/4/2020

1/5/2020

A

 

 

4

1/1/2020

1/6/2020

B

 

 

5

1/5/2020

1/7/2020

B

 

 

 

I want to calculate a new column 'Sum of WaitTime' which equals 0, 1, 2, 0, 4

Which is Sum(Current_InDate - InDate if(Current_InDate > InDate &Current_InDate < OutDate orOutDate is Null & Current_Area=Area) over ALL

The closest I have gotten in a calculated columnis:

if(([OutDate] is Null) or ([OutDate]>Max([inDate])),Sum(DateDiff("hh",[inDate],Max([inDate]))) over (Intersect(AllPrevious([inDate]),[Area])),Null)

There are a few things I know are wrong with this but I do not know where to go from here:

1. Max([inDate]) needs to be current node. MAX gives the overall max.

2. The 'if' being outside the aggregation means it is only calculated on rows that meet the criteria but it does not seem to allow 'ifs' inside an over statement.

I also tried a data function but it just runs forever (dataset is over 0.5 million rows):

Out

[Khushboo Rabadia]

You can achieve this using three calculated columns.

1) One for fetching previous indate value

2. Data > Add calculated column...

Column name: prevIndate

Expression: First([inDate]) OVER (Intersect([Area],Previous([item])))

2) One for fetching previous outdate value

3. Data > Add calculated column...

Column name: prevOutdate

Expression: First([OutDate]) OVER (Intersect([Area],Previous([item])))

3. Wait time. Once you have above two columns, you can just apply your condition and calculate datediff of days

4. Data > Add calculated column...

Column name: waittime

Expression: SN(If(([inDate]>[prevIndate]) and (([inDate]

[Austin Fox]

This works for the provided example but it was sadly insufficient. 

 

There can be many 'Items' that need to be included in the calculation of wait time not just the previous one. See this new example item 5:

 

Item	InDate	OutDate	Area	prevInDate	prevOutDate	waittime calc	waittime desired
1	1/1/2020	1/3/2020	A	Null	Null	0	0
3	1/2/2020	Null	A	1/1/2020	1/3/2020	1	1
4	1/4/2020	1/7/2020	A	1/2/2020	Null	2	2
5	1/5/2020	Null	A	1/4/2020	1/7/2020	1	4
2	1/1/2020	1/6/2020	B	Null	Null	0	0
6	1/5/2020	1/7/2020	B	1/1/2020	1/6/2020	4	4