Calculate time spent on sheets

[smiling_cheetah]

Hi everyone, quite in need of some help

[Usually I scout forums to find answers but this time couldn't] 

I'm trying to calculate time spent on each page of a document based on server logs and basically got files with the following structure:

User	Time	Type	Sheet	Object	Session
Tony	10:40	Open	SH10	Document	1
Tony	10:50	Activated Sheet	SH12	Sheet	1
Tony	11:00	Activated Sheet	SH15	Sheet	1
Tony	11:20	Activated Sheet	SH12	Sheet	1
Tony	11:30	Activated Sheet	SH10	Sheet	1
Tony	11:50	Close	SH10	Document	1

As an outcome I'm trying to get a table that looks like that:

User	Session	Sheet	TimeSpent
Tony	1	SH10	00:30
Tony	1	SH12	00:20
Tony	1	SH15	00:20

I get that I need to go from the first record to second (and so on), count the difference between timestamps and attribute that difference to the first Sheet. But struggling to figure out how to wrap it in QV code.

Any help would be really appreciated

[sunny_talwar · Accepted solution]

This?

Script:

Table:

LOAD User,

    Time(Time#(Time, 'hh:mm'), 'hh:mm') as Time,

    Type,

    Sheet,

    Object,

    Session

FROM

[https://community.qlik.com/thread/230379]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  Interval(If(User = Previous(User), Previous(Time) - Time, 0), 'hh:mm') as TimeSpent

Resident Table

Order By Time desc;

DROP Table Table;

[smiling_cheetah]

Thank you, that's just what i've needed