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

[farheenayesha]

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‌

[qlikview979]

Hi bro,

Its showing in timr format like HH:MM:SS but i want show them in minutes.

can u help me on this

Regards

Mahesh

[YoussefBelloum]

you can apply the Minute() function on Sunny's expression

minute - script and chart function ‒ QlikView

[sunny_talwar]

What do you mean... would you elaborate?

[sunny_talwar]

Got it... like this may be

[qlikview979]

Hi

For example

SH01 is showing 1:30:02 but i want to show 90:02(90min 2 sec)

Regards

Mahesh

[sunny_talwar]

strange way to look at this... anyways... try this

=Floor(Sum(Aggr(Below(Timestamp) - Timestamp, User, App, Date, (Key, (NUMERIC))))*60*24) & ':' & Num(Second(Sum(Aggr(Below(Timestamp) - Timestamp, User, App, Date, (Key, (NUMERIC))))), '00')

If you are satisfied, please have farheenayesha‌ close the thread by marking correct and helpful responses.

Best,

Sunny

[farheenayesha]

Hi Sunny,

     Sorry to bother you again. Your logic works perfectly for my requirement. However there is one challenge in the data because of which few sheets are not showing correct values.

The challenge is, few records doesnt have timestamp for open or close status (Details Column) .

Example: if the status of the User 1 is Open(2017-01-12 20:40:21), there is no close timestamp for the same.

Same with close. If the status of the User 1 is close(2017-01-11 02:02:36),  then it doesn't have timestamp for Open.

Is there any way to consider the Open as (2017-01-12 12:00:00)  and close as (2017-01-11 11:59:00) by default for the missing closed and open status.

Please find the attachment of the sample file where open and close status is missing for few records. Please help

[sunny_talwar · Accepted solution]

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));

[farheenayesha]

Thank you so much Sunny. You are great