Datediff - Different Columns - Across Rows

[CHL]

Hi All,

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.

Many Thanks!

[sasiparupudi1]

Interval([Time Move Start]-Above([Time Move End]),'d')

[PrashantSangle]

Hi,

use interval()

try like

interval(time2-time1,'D hh:mm')

Regards,

[CHL]

This does the same row I think? I need to to look at the previous row and the current row.

[sasiparupudi1]

I have updated the expression

[CHL]

As above: This does the same row I think? I need to to look at the previous row and the current row. Thank you

[CHL]

This looks promising! Does this take into account the Product ID?

[sasiparupudi1]

Interval([Time Move Start]-Above(TOTAL [Time Move End]),'d')

[MarcoWedel]

Hi,

maybe one solution could be:

table1:
LOAD * FROM [https://community.qlik.com/thread/297347] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 8))));

Join
LOAD RowNumber,
    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
Resident table1
Order By [Product ID], [Time Move Start];

hope this helps

regards

Marco