Skip to content

    • Beast Mode

    Beast Mode

    Average Count Distinct across Date Range

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

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

      1. COUNT(DISTINCT CASE 
      2.   WHEN (Team Member status = 'Active' OR Team Member status = 'Unpaid Leave') 
      3.     AND Date BETWEEN 'start_date' AND 'end_date' 
      4.    THEN Team Member ID END) 
      5. / 
      6. COUNT(DISTINCT Date)
    • Member
      edited January 2024

      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
    • Contributor

      @ncelis try using this instead of a between

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

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

    • 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?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories