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‌

[sunny_talwar]

I won't know until I see what you have.... based on the sample I am getting 9 hours 34 mins and 38 seconds on SH01.... if this is not right, what is the right number of SH01?

[sunny_talwar]

New Script

table:

LOAD Timestamp,

Date(Floor(Timestamp)) as Date,

    User,

    App,

    [Object/Action],

    [Object Id],

    Details,

    AutoNumber([Object Id])*1000000000&Timestamp as Key

FROM

[Time123.xlsx]

(ooxml, embedded labels, table is Sheet1);

New expression

=Sum(Aggr(Below(Timestamp) - Timestamp, User, App, Date, (Key, (NUMERIC))))

[YoussefBelloum]

what is the trick here sunny ?

multiply the Autonumber by 1000000000..

[sunny_talwar]

Just to give it higher weight then TimeStamp when I form the key.

[YoussefBelloum]

ok but the results are different on the barcharts for example, if we multiply or not

[sunny_talwar]

They can differ and to make it without errors, I would def. multiply Object ID or divide TimeStamp

[YoussefBelloum]

different results with one expression ? how can we know what is the correct result ?

[sunny_talwar]

The one with the multiplication or division will most likely give the right answer 99.999999% of the time. The other will depend on other factors. I can go in details at a later time

[YoussefBelloum]

ok thanks

[farheenayesha]

Hi Sunny,

    Your efforts and solution had solved my biggest problem. Thank you so much once again. Just last request, could you please help me to convert the time format HH:MM:SS to just minutes.