Forum Discussion
Production Time Analysis - Calculate Interval between sequenced Transactions.
I would like to calculate the interval between Transactions. See the example table below.
I'm looking for some trends on 'intervals' or 'waiting times' , and would like to look at a years worth of data, so could be 2.5 million transactions.
I'm trying to calculate the column H 'Interval'.
I have read about a few possible options, using index after grouping and offsetting, a calclated column or measure.
Ideally the calculated column is what i'd like to use, but a measure would be handy also.
Each UID is unique, but follows a sequence of operations within a batch. Each batch and number of transactions varies. All I really need is to identify the previous completed transaction for the UID and do the maths using the current start time of the record? Its all within the single table TAnal.
However, I have failed miserably so far, can anyone help or point to a solution somewhere?
Thank you for your support,
Much appreciated,
Proton
Pr0ton ,
Create the following column
Rank = Rankx(filter(Table, Table[UID] = earlier([UID]) ), [Start Time])
diff = if([Rank] <>1, datediff(Maxx(filter(Table, Table[UID] = earlier([UID]) && Table[Rank] = earlier([Rank]) -1), [Start Time]) , [Start Time], second) )
5 Replies
Pr0ton ,
Create the following column
Rank = Rankx(filter(Table, Table[UID] = earlier([UID]) ), [Start Time])
diff = if([Rank] <>1, datediff(Maxx(filter(Table, Table[UID] = earlier([UID]) && Table[Rank] = earlier([Rank]) -1), [Start Time]) , [Start Time], second) )
Thanks All,
I think this is now resolved. 😀
Thanks to amitchandak
1. Removed :- Schoolboy error in the data query which had unwanted 'almost duplicate' lines which prevented the Rank from counting correctly.
2. Tweaked the Datediff so that it uses [Completed Time] as Date1 , and Ranked-1 Earlier [Start Time] as Date 2 (which is actually the subsequent transaction as the ranking increments as it gets younger)
@gmsamborn Unfortunatley the Measure was too resource hungry for this dataset, but yes, on the sample you generated, works . Much appreciated for your time.
Many thanks again.
Hi Pr0ton
I realized that my previous measure didn't handle intervals greater than 24 hrs.
Again, this seems to work.
Interval 2 = VAR _CurrStart = SELECTEDVALUE( 'Data1'[Start Time] ) VAR _PrevClose = CALCULATE( MAX( 'Data1'[Completed Time] ), FILTER( ALLEXCEPT( 'Data1', 'Data1'[UID] ), 'Data1'[Start Time] < MAX( 'Data1'[Start Time] ) ) ) VAR _Diff = CONVERT( IF( _PrevClose, _CurrStart - _PrevClose ), DOUBLE ) VAR _Days = INT( _Diff ) VAR _Remainder = _Diff - _Days VAR _Hrs = INT( _Remainder * 24 ) VAR _Mins = INT( ( _Diff - ( ( _Days * 24 ) + _Hrs ) * 60 / 1440 ) * 1440 ) + 1 VAR _Result = IF( _Diff, _Days & " d " & _Hrs & " h " & _Mins & " m" ) RETURN _ResultMany Thanks, amitchandak. Its close but this is whats being returned.
I added as 2 columns, 1 for Rank, the other Diff? Is this correct?
The rank skips a line here and there, but I can see no missing lines in raw data. Diff is all negative when it dosent skip a rank.
Edited 5 mins later:- There must be missing lines as when i do a count of UID there are 43. I will do some more digging and come back to you. Thanks.
Hi Pr0ton
Would a measure like this help? It seems to work for me.
Interval = VAR _CurrStart = SELECTEDVALUE( 'Data1'[Start Time] ) VAR _PrevClose = CALCULATE( MAX( 'Data1'[Completed Time] ), FILTER( ALLEXCEPT( 'Data1', 'Data1'[UID] ), 'Data1'[Start Time] < MAX( 'Data1'[Start Time] ) ) ) VAR _Diff = IF( _PrevClose, _CurrStart - _PrevClose ) VAR _Result = FORMAT( _Diff, "hh:mm" ) RETURN _ResultLet me know if you have any questions.
