Can someone help me calculate employee turnover in Domo?

Reply
Visitor

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


Accepted Solutions
Black Belt

Re: Can someone help me calculate employee turnover in Domo?

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.

 

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Black Belt

Re: Can someone help me calculate employee turnover in Domo?

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.

 

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information