Forum Discussion
Teams Live Event Attendee Report - Track Attendee Duration
It would be helpful to have a sample sheet so we can see how the actual data is presented and ideally include an example of what you are trying to get out. But assuming the data is:
TimeStamp | UserID | Action
Assuming all users are in same columns of data as indicated above and that the log is complete and will have logon and exit for every user then to know:
how long an attendee joined:
=Sumifs( [TimeStamp], [UserId], user, [Action], "Exited"] - Sumifs( [TimeStamp], [UserId], user, [Action], "Joined"]
NOTE: this will add times together if the SAME UserID logged off and back on multiple times
how many people are in the webinar at any given time should be something like:
=Countifs( [TimeStamp], "<"&time, [Action], "Joined"] - Countifs( [TimeStamp], "<"&time, [Action], "Exited"]
MSWilko see attached. The concept I used was the same but:
a) made the original data table formatted as a table to use table references
b) added a new Date Time column because the original one is text instead of a Date Value
c) used MAP and LAMBDA to automatically calculate the full array instead of fill down
hope this helps
- mtarler
Hi, thank you so much for the attachment!
Unfortunately when I make any changes to the 'original data' tab (like pasting the new information) the date-time column H formula breaks. I try and paste that formula in again though returning #VALUE.
I am not great with excel - hence me posting on here - did you have any suggestions on how I can repair?
Thank you!does it need consider sum each session_id duration？
if so,you may try this online sql tool.
http://e.anyoupin.cn/ceshi/jstest/pull_up_demo.php?s=attendee
create temp table aa as
select *,julianday(udf_my_date_format(UTC_Event_Timestamp)) time from track_attendee_duration_by_Participant_Id;
create temp table bb as
select Full_Name,Session_Id,sum(iif(Action like 'left',time,-1*time)*24*60) duration,Session_Id,group_concat(UTC_Event_Timestamp) session_duration from aa group by Full_Name,Session_Id;
select Full_Name,round(sum(duration)) duration,group_concat(session_duration,'<br>') from bb group by Full_Name; 
