7 Replies Latest reply on Feb 17, 2018 8:50 AM by Jim Dehner

# Properly aggregating COUNTD expression

Hello all, I'm preparing a worksheet showing a simple metric, average number of phone calls per shift for several employees, compared against the overall calls/per shift mean average. My difficulty is that while i can the total number of shifts per employee on a row-by-row basis (using a COUNTD expression), I can't figure out how to aggregate the expression, in order to calculate and show an overall mean average figure as a standalone calculation (i.e. to show as a summary stat).

This is a simplification of what my data looks like:

A crosstab of the data shows that the employees worked a total of 7 shifts and answered 30 calls in total:

In Tableau, I can calculate everything i need in a table (a calls per shift average for each employee, and an overall mean average). My difficulty is with column 2 of the following view: number of shifts worked is calculated properly for each employee, but the total figure is wrong. The calculated field i'm using is Number of Shifts: COUNTD(DATEPART('dayofyear', [Date]))

I get that i can switch Total Using to Sum on the pill to show the correct total in the table, but I'm wondering how to reformulate my calculated field to produce the correct total automatically (which i need to be able to do to calculate a mean average outside this table). I've tried wrapping the calculated field in a TOTAL(), but this did nothing. Can someone show me the way forward here?

Workbook attached v10.5

-- Glenn

• ###### 1. Re: Properly aggregating COUNTD expression

Hey Glenn,

Oddly when I open your workbook it shows the correct number:

• ###### 2. Re: Properly aggregating COUNTD expression

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

1 of 1 people found this helpful
• ###### 3. Re: Properly aggregating COUNTD expression

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

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.

• ###### 5. Re: Properly aggregating COUNTD expression

The workbook i uploaded had Total Using switched to SUM, when i switch back to automatic the total is wrong:

-- glenn

• ###### 6. Re: Properly aggregating COUNTD expression

Perfect, thanks Jim.

-- glenn

• ###### 7. Re: Properly aggregating COUNTD expression

Thanks Glenn

Have a good weekend

Jim