I hope you can help!
I have some data which looks like the below and I am trying to track how long a product is in a location for.
|RowNumber||Product ID||Time Move Start||Time Move End||Time in Location|
|1||EB1234||01-01-2018 00:00||01-01-2018 00:10||-|
|2||EB1234||02-01-2018 03:00||02-01-2018 03:05||[02-01-2018 03:00] - [01-01-2018 00:10] = 1 Day|
|3||EB1234||04-01-2018 12:55||04-01-2018 13:00||[04-01-2018 12:55] - [02-01-2018 03:05] = 2 Days|
|4||AB987||04-01-2018 12:55||04-01-2018 13:00||-|
|5||AB987||05-01-2018 14:00||05-01-2018 14:05||[05-01-2018 14:00] - [04-01-2018 13:00] = 1 Day|
|6||EB1234||08-01-2018 01:00||08-01-2018 01:05||[08-01-2018 01:00] - [04-01-2018 13:00] = 4 Days|
So basically the time the last move ended is the time it was completed into its location and the time the next move started is the time it ended up leaving that location so the time in between is the dwell at location.
maybe one solution could be:
LOAD * FROM [https://community.qlik.com/thread/297347] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 8))));
If([Product ID]=Previous([Product ID]),Interval([Time Move Start]-Previous([Time Move End]),'d hh:mm')) as TimeInLocation,
If([Product ID]=Previous([Product ID]),Floor([Time Move Start])-Floor(Previous([Time Move End]))) as DaysInLocation
Order By [Product ID], [Time Move Start];
hope this helps