Jump to content
We've recently updated our Privacy Statement, available here. ×

How to find Sum of Wait Time time for manufacturing


Austin Fox

Recommended Posts

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...