Average Count Distinct across Date Range

ncelis
ncelis Member
in Beast Mode

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?

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @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
    ncelis Member
    edited January 29

    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:

    image.png

  • Jones01
    Jones01 Contributor

    @ncelis try using this instead of a between

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

  • ncelis
    ncelis Member

    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:

    image.png

  • MichelleH
    MichelleH Coach

    @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
    ncelis Member

    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).

    image.png

    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):

    image.png
    image.png

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

Categories