Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Data/Background
I have 3 tables linked together by fields.
One table contains (left table) call data including a unique call field. i.e. 2023-05-09T13:24:00+00:00. The other table (right table) contains the users in the call system (middle table contains call information not used in my table). For example;
| Caller ID | Name |
| Ext.01 | user 1 |
| Ext.02 | user 2 |
| Ext.03 | user 3 |
| Ext.04 | user 4 |
Issue
Once the tables are linked I can produce a table containing the call time, extension and name of the user associated with the call. The problem I have is trying to flag when a group call has occurred. One call at 10:00am may indicate that their was a team call in the morning which needs to be excluded from kpi metrics. Currently my sheet contains a table looks like the below:
| Call details | Caller ID | Name
| 2023-05-09T13:24:00+00:00 | Ext.01 | User 1
| 2023-05-09T13:24:00+00:00 | Ext.02 | user 2 |
| 2023-05-09T13:24:00+00:00 | Ext.03 | user 3 |
| 2023-05-09T13:24:00+00:00 | Ext.04 | user 4 |
| 2023-05-10T15:24:00+00:00 | Ext.04 | user 2 | // Different call
| 2023-05-11T10:00:00+00:00 | Ext.02 | user 2 | // Different call
Ideal Solution
How do I add a column on the end that totals the number of times the call exists within a table chart in a sheet? Like so:
| Call details | Caller ID | Name | Participants in call |
| 2023-05-09T13:24:00+00:00 | Ext.01 | User 1 | 4 |
| 2023-05-09T13:24:00+00:00 | Ext.02 | user 2 | 4 |
| 2023-05-09T13:24:00+00:00 | Ext.03 | user 3 | 4 |
| 2023-05-09T13:24:00+00:00| Ext.04 | user 4 | 4 |
| 2023-05-10T15:24:00+00:00 | Ext.04 | user 4 | 1 |
| 2023-05-11T10:00:00+00:00 | Ext.02 | user 2 | 1 |
I tried a set analysis expression but could only ever get the number 1.
I have also thought about joining the tables which I know would solve this issue but I figured this would crop up again at some point in my career and I may not be able to join tables.
Perhaps:
Count(total <[Call Details]> distinct Name)
Thank you very much for the swift response! I very much appreciate it!