Calculating Employee Attrition Ratio

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Hi, I have a connected table 'Employees' from Intacct where from I have pulled in employee data with the Start Date and End date. Start date means joining date and End date means the date employee left. I need to produce an attrition ratio report by month with rolling 12 months. Every month the period of calculation would be for example , Jan'16 (period 02/01/2015 to 01/31/2016), Feb'16 (period 03/01/2015 to 02/29/2016), Mar'16 (period 04/01/2015 to 03/31/2016)....and it goes on...

The formula is

Attrition ratio = [Employee left during the period / ((Opening Balance of head count at the start of the period + Closing balance at the end of the period) / 2)


How should I do that? And which report selection would be best in this case?

Photo of Ankur

Ankur

  • 0 Points

Posted 3 years ago

  • 0
  • 1
You cannot do this with a simple report.  You will need for set up a single record in a table called Attrition Calculations.  It will be [Record ID#] of 1.  The will only ever be one record in this table so set the permissions so that after you add the record, no one, even you can add a record.

In your employees table make a numeric formula field with a formula of 1.  Make a relationship to the Attirition Calculatios table based on that feld.

Then you will need to build a set of 12 fields on the Attrition record.  Each set will consist of fields for a different trailing month.

A formula to calculate the first day of the month for that month.  You will need to lookup those 12 Months down to the child records.

The floating month name based on formula off the first day of the month fields. For example you need to calculate the name of the month for each of the trailing 12 months?

A summary field of the number of employees who were terminated during that month.  Ie the FirstDayOfMonth[End Date] is equal to day the field [Current month -1]. You may want to have 12 fields each named like that, and holding the first day of each of the previous 12 months.

Summary field of the number of employees who were active during that month.
Summary field of the number of employees who were active 12 moths ago.


... So basically you need to build these calculations all in that Attrition record.
Photo of Ankur

Ankur

  • 0 Points
Thanks Mark for the reply. I'm still learning on Quickbase stuff and to be honest, I have understood partially. My question may sound like silly but please pardon me. 

When you said to make a formula field in employees table, that numeric formula is to identify the first day of the month employee has left, correct?   

And on relationship, I have to make 'Attrition Calculation' the parent and "Employees" the child, correct?

Can you please elaborate on the set of 12 fields concept?
Re:
When you said to make a formula field in employees table, that numeric formula is to identify the first day of the month employee has left, correct?     Yes exactly. FirstDayOfMonth(Employee Termination Date[)

Yes, Attrition calculation is the parent.

Nevermind the 12 "sets" for now.  Just get the first set working.

Make a field on the Parent that calculates the FirstDayofMonth(Today())

Then see if you can get that first Attrition calculation done for the most recent 12 month period.