topic Calculate time spent on sheets in QlikView

Calculate time spent on sheets

smiling_cheetah — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate time spent on sheets

sunny_talwar — Thu, 25 Aug 2016 13:39:27 GMT

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;

Re: Calculate time spent on sheets

smiling_cheetah — Thu, 25 Aug 2016 16:15:29 GMT

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