If you use above(total), you will get the previous line disregarding the column sections (new ItemID).
I would suggest that you remove the CreationDate from the dimensions, using an Expression instead (it's a 1:1 relation to ProcedureID anyway, right?), then your Delta ProcedureTime expression could look like
and the average ProcedureTime:
=Interval(avg(total<ItemID> aggr((CreationDate)-Above( CreationDate),ItemID,ProcedureID)))
To make the last expression work, your ProcedureIDs should have the correct load order (chronological), as in your input file (because aggr() will order by load order).
See also attached,
Test_ProcedureTimes.qvw 165.0 K
Thank you for your Reply.
i will clear one thing.
The Item is Moving from source to Destination by passing through the diffrent ProcedureID's.
when an item is moving from One ProcedureID To another ProcedureID i need to calculate time interval between those two ProcedureID's, and at the end of that of the ProdedureID i need to calculate Total Avg time to reach the Item From Source to Destination.
in below attached you can see the Item Movments, from Different ProcedureID's.
and May be sometimes ProcedureID's are not Order.means
an item can move first ProcedureID=54 then ProcedureID=35 and so on.
below attached is the scenario of the ItemID=107 which is moving from Different ProceDureID's.
when item is move from ProcedureID=35 to 38 i need the Time Difference.(00:00:03) (3 seconds) and so on.
test.pptx 37.6 K
I created a new field in the script that allows to order your second dimesion ProcedureID accordingly.
Thus I think the first expression should work fine now, not 100% sure about the second (the average).
Test_ProcedureTimes_2.qvw 167.0 K