Turnover Beast Mode: Avg Distinct Count / Days in date range

Options
ncelis
ncelis Member
in Beast Mode

I am attempting to create a Turnover Rate beast mode that would take the Average Distinct Count of employee IDs, then divide it by the number of days in the user selected date range.

I've tried the following syntax, but I receive an error:

image.png

Raw syntax:

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

Does anyone have experience with Turnover in Domo? Am I missing something obvious in my syntax?

Tagged:

Best Answer

  • nmizzell
    nmizzell Contributor
    edited January 30 Answer ✓
    Options

    You need to use Magic ETL Rank and Window tile to get turnover rate. Here is how you do it:

    1. Take the cumulative sum of employees hired (can be by department, job title, or cost center, as needed). This will be done using the rank and window tile (settings = sum preceding unbounded to following zero) by counting employee ids by their hire date, partitioning by department, job title, etc.
    2. Sum the total employees terminated by day.
    3. Group by the desired time interval. For example, if you want to see monthly turnover rate, group the cumulative sum of employees using AVG, and the terminated number of employees using SUM. Include the partition in the group by, if applicable.
    4. Turnover Rate = SUM Terminated Employees / AVG Cumulative employees
    5. Create your card. I suggest line chart or mega table with partition as rows, date as columns, turnover rate heat mapped.

    P.S You should include supplemental information alongside these visuals, such as Most recent hires and terminations by department, title, etc. Total headcount by department, title, etc. This will give the viewer a more complete understanding of employee turnover.

Answers

  • nmizzell
    nmizzell Contributor
    edited January 30 Answer ✓
    Options

    You need to use Magic ETL Rank and Window tile to get turnover rate. Here is how you do it:

    1. Take the cumulative sum of employees hired (can be by department, job title, or cost center, as needed). This will be done using the rank and window tile (settings = sum preceding unbounded to following zero) by counting employee ids by their hire date, partitioning by department, job title, etc.
    2. Sum the total employees terminated by day.
    3. Group by the desired time interval. For example, if you want to see monthly turnover rate, group the cumulative sum of employees using AVG, and the terminated number of employees using SUM. Include the partition in the group by, if applicable.
    4. Turnover Rate = SUM Terminated Employees / AVG Cumulative employees
    5. Create your card. I suggest line chart or mega table with partition as rows, date as columns, turnover rate heat mapped.

    P.S You should include supplemental information alongside these visuals, such as Most recent hires and terminations by department, title, etc. Total headcount by department, title, etc. This will give the viewer a more complete understanding of employee turnover.

Categories