Employee Turnover Report

user033862

I am trying to create an employee turnover report using a master list of employees, all time, with hire date, term date current status etc. Ideally would be able to use this as a running report to be able to see a snap shot of a time in the past - looking for active at start, # of new hires, # of terminations, active at end and turnover percentage. Not quite sure where to start, new to domo, have tried to just get a simple count of hire dates and term dates using some beast mode calc I found through searches here but none seem to work.  Can anyone help, data sample is attached.

GrantSmith

Hi @user033862 

 

Depending on how granular you'd like the snapshots and the number of employee records this could end up being a lot of records. What I'd recommend is having a dataflow run every so often (probably monthly unless you need it more granular) where it could calculate the number of employees, hires, terminations and status. You'll also want to include the current timestamp in the field when this runs to graph against later.. You can then feed this into a snapshot dataflow to record this when it's finished running. You can then display the aggregated data over time using the timestamp field.

 

Here's a link to the article which describes a recursive snapshot dataflow: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_ETL_DataFlow

 

 

GrantSmith

If you're using ETL 2.0 the recursive portion will be a bit different since it handles NULLs differently than ETL 1.0.

 

The Magic ETL 2.0 documentation can be found here: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_DataFlow_in_Magic_ETL_v2_(Beta) 

jaeW_at_Onyx

"Depending on how granular you'd like the snapshots and the number of employee records this could end up being a lot of records. What I'd recommend is having a dataflow run every so often (probably monthly unless you need it more granular) where it could calculate the number of employees, hires, terminations and status."  

 

I strongly disagree with / advise against this approach because IF you build a recursive dataflow on an aggregate (GROUP BY MONTH and calculate headcount, hires, terminations etc.) THEN you lose flexibility if you want to change or add new metrics (ex. how many employees changed roles within the company).

 

The more flexible (but still similar) solution would be to take a monthly (or whatever frequency) recursive snapshot of the entire Employee report.  Then apply logic to determine if the employee was there last month (isNewHire), if the employee is missing next month (isTerminated) or if the employee's job changed from this month to next month (isNewRole).

 

Similar but not same question here:  https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Calculating-the-Running-Maximum/m-p/51132