-
1. Re: Properly aggregating COUNTD expression
Neil Lord Feb 17, 2018 7:50 AM (in response to Glenn Kuly) -
2. Re: Properly aggregating COUNTD expression
Norbert MaijoorFeb 17, 2018 8:09 AM (in response to Glenn Kuly)
Hi Glenn,
Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread
1. NM M0. Callcounts per Employee: {fixed [Call Answered By]:sum([CallCount])}
// count calls per employee
2. NM M1. Number Of Shifts: {fixed: countd([Date])}
// count distinct days in dataset
3. NM M2. Nr Of Shifts per Employee: {fixed [Call Answered By]: countd([Date])}
//count distinct days per Employee
4. NM M3. Avg Calls/Shift/Employee: [NM M0. Callcounts per Employee]/[NM M2. Nr Of Shifts per Employee]
//divide Call counts per Employee by the number of shifts per Employee
5. NM M4. Avg Calls/Shift: {fixed: sum([NM M3. Avg Calls/Shift/Employee])/countd([Call Answered By])}
// divide the sum of average Calls per Employee by amount of Employees
6. NM M5. Staff Diff From Avg Calls/Shift: [NM M3. Avg Calls/Shift/Employee]-[NM M4. Avg Calls/Shift]
7. Drag the required objects to the indicated locations.
Regards,
Norbert
-
Callcount_nalmai_10.3.twbx 28.2 KB
-
-
3. Re: Properly aggregating COUNTD expression
Jack Watts Feb 17, 2018 8:21 AM (in response to Glenn Kuly)Hey Glenn,
Your problem, from what I can tell, is that you are counting unique days, but because employees may have employees working on the same day, you need a way to look at each employees unique days and then add those up. Try this:
sum({ FIXED [Call Answered By]: countd(DATEPART('dayofyear', [Date]))})
I checked the second sheet with just the number and it shows 7.
EDIT: The person above did some more calculations - this is just a solution to get the number for the number of shifts you mentioned in your post.
Hope this helps,
Jack
-
4. Re: Properly aggregating COUNTD expression
Jim DehnerFeb 17, 2018 8:27 AM (in response to Glenn Kuly)
Hi Glenn
then fixed number of shifts is
if you wan tot have a column with individual values that total to the fixed number that formula is
You can used the fixed number in other calculations or on other sheets
in chart form it returns
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
Book1 (27)_v10.2.twbx 18.6 KB
-
-
5. Re: Properly aggregating COUNTD expression
Glenn Kuly Feb 17, 2018 8:47 AM (in response to Neil Lord) -
6. Re: Properly aggregating COUNTD expression
Glenn Kuly Feb 17, 2018 8:49 AM (in response to Jim Dehner)Perfect, thanks Jim.
-- glenn
-
7. Re: Properly aggregating COUNTD expression
Jim DehnerFeb 17, 2018 8:50 AM (in response to Glenn Kuly)
Thanks Glenn
Have a good weekend
Jim