Solved: How to calculate time spent by user on each Dashbo... - Qlik Community

farheenayesha · ‎2018-01-23

Hi,

I am trying to calculate, how much time a user has spent on each dashboard and sheet.

Example: In the below image, i want to calculate time spent (in minutes) by user 3 on dashboard 2 and also time spent in minutes on SH01, SH02 and SH03.

The above example is just for one user. However I want to calculate for all the users. Please help me.

Please find the data attached. youssefbelloum‌

sunny_talwar · ‎2018-01-25

Concatenate those values at the end

Table:

LOAD Timestamp,

User,

App,

Object/Action,

Object Id,

Details,

AutoNumber(User&App&Object Id) as CheckKey

From ....;

Temp:

LOAD DISTINCT

User,

App

Resident Table;

Left Join

Load * Inline [

Details

Open

Close

];

Concatenate(Table)

LOAD *,

If(Details = 'Open', MakeDate(2017, 1, 12) + MakeTime(12), MakeDate(2017, 1, 11) + MakeTime(11, 59)) as Timestamp

Resident Temp

Where Not Exists(Key, AutoNumber(User&App&Object Id));

View solution in original post

YoussefBelloum · ‎2018-01-23

Can you extract this table on excel for me please ?

farheenayesha · ‎2018-01-23

Sorry forgot to attach the excel. Please find the attachement

shiveshsingh · ‎2018-01-23

If we take user 1, for that user doc was opened two times..Do you want to subtract the time between first closed and second open?

Timestamp	User	App	Object/Action	Object Id	Details
2017-06-15 14:00:50	User1	Dashboard 1	Document	Document\SH01	Open
2017-06-15 14:01:02	User1	Dashboard 1	Document	Document\SH01	Close
2017-06-15 14:01:10	User1	Dashboard 1	Document	Document\SH01	Open

sasikanth · ‎2018-01-23

Hi Ayeesha,

use the below expression in Straight table

Dim1: App

Dim2:ObjectID

Exp: interval ( ( Aggr(max(Date(Timestamp)),[Object Id]) - Aggr( min (Date(Timestamp)),[Object Id])) ,'mm')

If you wan to display result in a text object based on selections then you can simple write

=Interval(max(Timestamp)-min(Timestamp),'mm')

farheenayesha · ‎2018-01-23

Hi Shivesh,

Thanks for the quick reply. Actually i want to calculate the total time spent on each sheet. Hence in this case i want to take (FirstClose-FirstOpen) + (SecondClose-SecondOpen) because there is some time gap between firstclose and secondOpen.

Please let me know if you need more information. Kindly help

YoussefBelloum · ‎2018-01-23

Hi,

here is one method to do it:

find attached

qlikview979 · ‎2018-01-23

Hi ,

Can you please share your Excel file.

Regards

Mahesh

YoussefBelloum · ‎2018-01-23

I used Ayeesha's file

farheenayesha · ‎2018-01-23

Hi Youssef,

Thanks for your reply. Your logic is working perfectly for the table. However i want to get the same result in the bar chart. I want to take Object id as the dimension and want to show time (in minutes) for each Object id. Kindly help

How to calculate time spent by user on each Dashboard & Sheet