Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
harjit_nar
New Contributor

Calculating Time Difference between multiple timestamps

I have a table similar to this.

IDDateTime

1

1/10/1709:00
11/10/1710:00
11/10/1711:00
11/10/1712:00
21/10/1705:00
21/10/17

05:15

21/10/1705:30
21/10/1705:45
21/10/1706:00


I want to calculate the average time difference per per ID, Date e.g. 60mins or 15 mins.


Can anyone help me do this within the script?

Tags (3)
2 Replies

Re: Calculating Time Difference between multiple timestamps

This should do the trick:

Data:

LOAD * INLINE [

    ID, Date, Time

    1, 1/10/17, 09:00

    1, 1/10/17, 10:00

    1, 1/10/17, 11:00

    1, 1/10/17, 12:00

    2, 1/10/17, 05:00

    2, 1/10/17, 05:15

    2, 1/10/17, 05:30

    2, 1/10/17, 05:45

    2, 1/10/17, 06:00

];

Difference:

LOAD ID,

Date,

Time,

IF(ID = Previous(ID) AND Date = Previous(Date), Interval(Time - Previous(Time), 'mm')) as Diff

RESIDENT Data

ORDER BY ID, Date, Time;

Average:

LOAD ID,

Date,

Interval(Avg(Diff), 'mm') As Average

RESIDENT Difference

GROUP BY ID, Date;

DROP TABLES Data, Difference;

giakoum
Honored Contributor II

Re: Calculating Time Difference between multiple timestamps

combine firstsortedvalue with -Date for max and Date for min and then just subtract the 2

something like:

load

Timestamp(firstsortedvalue, -Date) - Timestamp(firstsortedvalue, Date) as diff

Resident table

Group by

ID, Date