Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
you can apply the Minute() function on Sunny's expression
What do you mean... would you elaborate?
Got it... like this may be
Hi
For example
SH01 is showing 1:30:02 but i want to show 90:02(90min 2 sec)
Regards
Mahesh
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
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
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));
Thank you so much Sunny. You are great