Can someone help me calculate employee turnover in Domo?

I'm relatively new to Domo and wanted to reach out to you about calculating Employee Turnover rate. I saw a previous thread (https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Turnover-Calculation-Beast-Mode-Help/td-p/31638) but wanted to see if someone could give me some tips on this specifically. 

 

I'm looking to get our turnover rate and have it be dynamic so that I can click between certain dates and have it be accurate. I've been asked by our Talent Design Manager to do this as he'd like to be able to click around and find out all turnover-related numbers as they relate to a time period (monthly, quarterly, yearly), department, etc... I'd love to be able to figure this out with someone's help!

 

I've included a sample picture of the data/columns that I have in my google sheet.

 

Thank you very much in advance!

 

2019-05-01_11-50-08.png

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    This required me to use a dataflow.  I am more comfortable using MySQL, so that is how I built this data set.  If you would prefer me to walk through the ETL side of things, please let me know.

     

    Step 1:

    You need to add a calendar data set.  This dataset needs to list every day that you would be interested in being able to analyze.  Domo offers their own calendar data set which you can obtain by:1.png

     

     

    This data set only goes back to Jan 1, 2010.  If you need to go further back, you will need to obtain another calendar data set.

     

    Once you have your calendar data, we can move on to adding a MySQL data flow.

     

    Step 2:2.png

    Transform 1:

    SELECT
    `dt` as `Date`
    FROM `calendar`

    I saved that as "dates_table"

     

    Transform 2:

    SELECT
    k.`ID`,
    k.Name,
    k.Title,
    k.Department,
    k.Location,
    k.Team,
    k.Manager,
    k.`Hire Date`,
    k.`Termination Date`,
    k.`Termination Type`,
    k.`Terminations`,
    k.`Current Employees`,
    k.`Status`,
    d.`Date`,
    case
    when d.`Date` >= k.`Hire Date` and `Termination Date` is null then 'Active'
    WHEN d.`Date` >=k.`Hire Date` and d.`Date` < k.`Termination Date` then 'Active'
    WHEN d.`Date` = k.`Termination Date` then 'Termination'
    END as `Status on Date`
    FROM `dojo_kstevens1` k
    LEFT JOIN `dates_table` d
    ON ((k.`Hire Date`<= d.`Date` AND k.`Termination Date` is null)
    OR (k.`Hire Date`<= d.`Date` and k.`Termination Date` >= d.`Date`))
    AND d.`Date` <= CURDATE()

    I forgot to name this table, so it is called "transform_data_1"

     

    for the output table:

    SELECT * FROM transform_data_1

    This will let you create the chart you are looking for with a few beastmodes.

     

    Beastmode #1:

    Active ID Count

    COUNT(DISTINCT case when `Status on Date`='Active' then `ID` end)

    Turnover Rate

    COUNT(DISTINCT  
    case
    when `Status on Date`='Termination' then `ID` end)
    /
    COUNT(DISTINCT
    case
    when `Status on Date`='Active' then `ID` end)

     

    Here is my chart, you can dynamically pick any two dates by selecting the "between" option under the date drop down and you will get a line chart showing the number of employees by day (or week/month/etc. if you would like) The summary number will display the Turnover rate for the period selected.  You also have the option to filter on location or department.4.png

     

     

    Hope that helps.  

     

    FYI, your data set will get pretty large.  You are essentially creating a row of data for every day of employment, for every employee.  This is required if you need to be able to see the turnover rate between any two dates.  You could reduce the data set if you only need to be able to select any two weeks, or any two months.

     

     

Answers

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    This required me to use a dataflow.  I am more comfortable using MySQL, so that is how I built this data set.  If you would prefer me to walk through the ETL side of things, please let me know.

     

    Step 1:

    You need to add a calendar data set.  This dataset needs to list every day that you would be interested in being able to analyze.  Domo offers their own calendar data set which you can obtain by:1.png

     

     

    This data set only goes back to Jan 1, 2010.  If you need to go further back, you will need to obtain another calendar data set.

     

    Once you have your calendar data, we can move on to adding a MySQL data flow.

     

    Step 2:2.png

    Transform 1:

    SELECT
    `dt` as `Date`
    FROM `calendar`

    I saved that as "dates_table"

     

    Transform 2:

    SELECT
    k.`ID`,
    k.Name,
    k.Title,
    k.Department,
    k.Location,
    k.Team,
    k.Manager,
    k.`Hire Date`,
    k.`Termination Date`,
    k.`Termination Type`,
    k.`Terminations`,
    k.`Current Employees`,
    k.`Status`,
    d.`Date`,
    case
    when d.`Date` >= k.`Hire Date` and `Termination Date` is null then 'Active'
    WHEN d.`Date` >=k.`Hire Date` and d.`Date` < k.`Termination Date` then 'Active'
    WHEN d.`Date` = k.`Termination Date` then 'Termination'
    END as `Status on Date`
    FROM `dojo_kstevens1` k
    LEFT JOIN `dates_table` d
    ON ((k.`Hire Date`<= d.`Date` AND k.`Termination Date` is null)
    OR (k.`Hire Date`<= d.`Date` and k.`Termination Date` >= d.`Date`))
    AND d.`Date` <= CURDATE()

    I forgot to name this table, so it is called "transform_data_1"

     

    for the output table:

    SELECT * FROM transform_data_1

    This will let you create the chart you are looking for with a few beastmodes.

     

    Beastmode #1:

    Active ID Count

    COUNT(DISTINCT case when `Status on Date`='Active' then `ID` end)

    Turnover Rate

    COUNT(DISTINCT  
    case
    when `Status on Date`='Termination' then `ID` end)
    /
    COUNT(DISTINCT
    case
    when `Status on Date`='Active' then `ID` end)

     

    Here is my chart, you can dynamically pick any two dates by selecting the "between" option under the date drop down and you will get a line chart showing the number of employees by day (or week/month/etc. if you would like) The summary number will display the Turnover rate for the period selected.  You also have the option to filter on location or department.4.png

     

     

    Hope that helps.  

     

    FYI, your data set will get pretty large.  You are essentially creating a row of data for every day of employment, for every employee.  This is required if you need to be able to see the turnover rate between any two dates.  You could reduce the data set if you only need to be able to select any two weeks, or any two months.

     

     

  • @ST_-Superman-_ 

     

    Your Solution works amazing and I'm still getting used to creating BeastModes!

     

    I would like to use this function to calculate Turnover Rate

    COUNT(DISTINCT  
    case
    when `Status on Date`='Termination' then `ID` end)
    /
    COUNT(DISTINCT
    case
    when `Status on Date`='Active' then `ID` end)

     however, we calculate it using average Headcount, but when I tried to wrap an AVG() function around the denominator the card throws an error.

     

    How would you recommend I Average the denominator?

     

    The reason this would be useful is because I would like it to dynamically adjust when I change the Graph By in the Date Range.  i.e. Changing from Days to Months, to Years etc.

     

     

  • you can simplify this code.

     

    if i'm reading it correctly, the goal is to generate one row for each employee during the dates of their employment

     

    SELECT
    ...
    k.`Status`,
    d.`Date`,
    case
    WHEN d.`Date` = k.`Termination Date` then 'Termination'
    ELSE 'Active'
    END as `Status on Date`

    ### to make this column summable, I would just implement it as a binary (1 for employed, 0 for not).
     case
    WHEN d.`Date` = k.`Termination Date` then 0 ELSE 1 END as `daily_Headcount`
    ####

    FROM `dojo_kstevens1` k
    LEFT JOIN `dates_table` d
    #### to make this simpler to read, combine the Termination Date JOIN ###
    ON
    k.`Hire Date`<= d.`Date` AND
    ( k.`Termination Date` is null OR k.`Termination Date` >= d.`Date`) AND
    d.`Date` <= CURDATE()
    ### to make the JOIN clause even simpler hard code the Termination Date as CURDATE() + 90 or the end of the current year.
    ex. add a preceeding transform where you create a clean Termination_date that you can send to your output dataset.

    SELECT *,
    coalesce(
    `Termination Date`,
    LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH))) ## end of year
    ) as `Termination Date_clean`
    FROM dojo_kstevens1

    then in your output

    SELECT
    ...
    k.`Status`
    , `Termination Date_clean`
    , d.`Date`
    , case
    WHEN d.`Date` = k.`Termination Date_clean` then 'Termination'
    ELSE 'Active'
    END as `Status on Date`
    ,  case
    WHEN d.`Date` = k.`Termination Date_clean` then 0
    ELSE 1 END
    as `daily_Headcount`
    FROM `dojo_kstevens1_modified` k
    LEFT JOIN `dates_table` d
    ON
    k.`Hire Date`<= d.`Date`
    AND k.`Termination Date__clean >= d.`Date` 

     

    Regarding your avg headcount, 

    you can either code it into your ETL using a windowed function or you can write your windowed function into a beast mode.

     

    ## avg headcount per month

    sum(sum(daily_headcount)) over (partition by year(date), month(date)) /

    count(sum(1)) over (partition by year(date), month(date))

     

    you'll need aggregate functions enabled in your instance of domo, and pay attention to whether 

    the denominator is returning the number of days per month as expected.

     

    https://www.youtube.com/watch?v=cnc6gMKZ9R8

    https://www.youtube.com/watch?v=eifSYZIcPzg&t=2240s

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • ST_Superman
    ST_Superman Domo Employee

    You should be able to use something like this:

    COUNT(DISTINCT case when `Status on Date`='Active' then concat(`ID`,`Date`) end)
    /
    COUNT(DISTINCT `Date`)

     

    This will let you graph the Average headcount for any period instead of just the active headcount.

    avg head count.png

     

  • This works beautifully!  Thank you!

  • I'll have to give this a try!

  • @ST_-Superman-_

    Just taking over our Domo for our department and was looking to update the headcount information. Found this and it is awesome so just wanted to say thanks.

  • Derreck
    Derreck Member
    edited May 2022

    @GrantSmith @jaeW_at_Onyx @ST_-Superman-_

    Thank you for providing the clear detail! Worked perfectly!


    does anyone have any suggestions on building an LTM rolling turnover rate beastmode?


    I have window functions enabled in my instance.


    Thank you!