2 Replies Latest reply on Oct 3, 2018 6:17 AM by Elizabeth Perry

# Count distinct across months and years

HI, I have transactional level expenses data which shows individual expenses claimed by an employee each row shows the employee, the date claimed, the amount and the employees manager. I provide yearly totals at manager and employee level and have been asked to show the total number of individuals under each manager. I've been experimenting with countd() but I run into problems when I try to add an if statement to this, for example IF ([Year] = '2018' THEN COUNTD([EmployeeID]), it tells me I can't mix aggregate with non aggregate. I don't want to show all measures per year so I have created calculated fields for expenses for 2017 & 2018 I've then created a field to calculate the difference and the variance between years. I want to be able to do the same for total employees. I've created a very basic example of the data I'm dealing with, each employee can appear under different managers as time passes. Obviously if I total distinct employees at manager level in my example I'd want to see a total per 2017 & 2018 but then I'd also want it to react to the month filter I have on the workbook. TIA.

• ###### 1. Re: Count distinct across months and years

Hi,

Are you looking for this??

2018 Distinct = COUNTD(IF year([Sent for Payment Date]) = 2018 THEN ([Employee Custom 7 - Name]) END)

2017 Distinct = COUNTD(IF year([Sent for Payment Date]) = 2017 THEN ([Employee Custom 7 - Name]) END)

Employee difference = [2018 Distinct] - [2017 Distinct]

• ###### 2. Re: Count distinct across months and years

Brilliant thanks, I was experimenting with aggregating the entire calculation but obviously messed up my syntax! Thanks!