Average Count Distinct across Date Range

ncelis

I am working on Turnover metrics and need to capture the average unique headcount of employees over a user selected date range.

Here is what I've tried, but Domo keeps giving errors:

COUNT_DISTINCT(CASE WHEN (Team Member status = 'Active' OR Team Member status = 'Unpaid Leave') AND Date BETWEEN 'start_date' AND 'end_date' THEN Team Member ID END) / COUNT_DISTINCT(Date)

Does anyone else have experience with this?

MichelleH

@ncelis It looks like you're not using the correct syntax for the count distinct function. The word "Distinct" needs to be inside the parenthesis like below:

COUNT(DISTINCT CASE 
  WHEN (Team Member status = 'Active' OR Team Member status = 'Unpaid Leave') 
    AND Date BETWEEN 'start_date' AND 'end_date' 
   THEN Team Member ID END) 
/ 
COUNT(DISTINCT Date)

ncelis

Thanks @MichelleH, but it looks like Domo is still throwing an error. Here is your calc with my specific field names, and the error message:

Jones01

@ncelis try using this instead of a between

AND Date Scaffold >= Event Date and Date Scaffold <= adjusted_end_date

ncelis

Thanks @Jones01 but now I receive an error stating that one of the columns does not exist (event though all columns we're recognized in previous calc). I wish Domo error messages were more descriptive:

MichelleH

@ncelis Try changing the double quotes to single quotes around 'Active' and 'Unpaid Leave'. Domo expects string fields to have be surrounded by single quotes.

ncelis

So I am closer to a solution using this Beast Mode:

COUNT(DISTINCT CASE
WHEN (Team Member Status = 'Active' OR Team Member Status = 'Unpaid Leave')
THEN Team Member ID END)
/
COUNT(DISTINCT Date Scaffold)

This "Average Headcount" now matches my "Active Headcount" totals on a daily basis which is great! (There are 2 lines in the viz, but are the same amounts so it appears like 1 line).

However, when I switch the visual to "Graph by" week or month, it appears the new "Average Headcount" calc is not working as intended (totals are way too low):

Any ideas on why my "Average Headcount" would be so low?