3 Replies Latest reply on Aug 19, 2018 8:55 PM by GAO Yuanyang

# How to calculate attrition rate of every month

Hi everyone,I am a newbie .This is my first time here.

My BOSS ask me to analyze the company's  attrition rate of each department in every month.I  failed in calculate  each month  results.

Can my probem be solved?

first, month attrition rate=(Number of quit this month)/(average number this month)

eg:

May 1st  : staff number 100

Jun 1st: staff number 70

from may 1st to Jun 1st ,40 staffs quit an 10 new guys in

the attrition rate=40/（100+70）*2=47%

date i have like these

HR record : it record sum( staffs) in the start month and the end month which i need to calculate the attrition rate between

staff quit list: records the details of staffs such as quit time

HR-RECORD(if nessary I can get records of each month)

 record time department staff number 2018/7/1 A 16 2018/7/1 B 24 2018/7/1 C 17 2018/1/1 A 19 2018/1/1 B 21 2018/1/1 C 16

QUIT-LIST

 ID department leave time NO.068 A 2018/1/22 NO.180 A 2018/3/21 NO.181 A 2018/5/17 NO.183 A 2018/1/9 NO.188 B 2018/4/28 NO.204 A 2018/3/1 NO.206 A 2018/3/1 NO.213 A 2018/3/1 NO.008 C 2018/5/16 NO.038 C 2018/6/11 NO.043 C 2018/6/27 NO.058 C 2018/6/29

I made a sample  to solve this problem like this  in the attchment

I wonder if I connect more month record data ,how can I join the data .and how to get right result

[the table above shows the attrition rate from 2018/1/1 to 2018/7/1]

[THIS table is what i need but I know it is wrong because I didn't give more month records]

I really need some help.

My English is not good ,wish you can understand my question.

Thank you !

• ###### 1. Re: How to calculate attrition rate of every month

Hi GAO,

One way to get the result (there may be other ways, though)

is to have the datasource made by two tables --

the one would be a periodic (monthly) snapshot table

of the Headcount for each Department & Month combination

(you've got them as the HR-record-moremonth Excel sheet),

the other would be your Quit List (a transaction table).

Joining both tables ON Department and Month would give a dataset to start with.

The view could be built using the LOD calculation

based on the columns from the periodic snapshot table,

the COUNTD() aggregation from the transaction table

and the final Ratio calc using the above two.

Smoothing the result (by averaging the two consecutive months in the denominator)

could be made by using a Table Calculation function LOOKUP() along the Date Dimension.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: How to calculate attrition rate of every month

Thank you very much.

THIS is a really wonderful join for me and I will never work out with out your help.

AS I can see, attrition=a/(b+c)*2, add up "a" is easy for me now. but how to calculate b and c is hard for me

because they can not be added up , they should be"moving", for example IF I calculate the attrition between JAN and MAY , the"a" is add up,and the "b"should be

JAN  and the "c" should move on to the MAY

Can you  do me a favor again?

thank you very much for your wonderful help

• ###### 3. Re: How to calculate attrition rate of every month

I study the function in the attchement . I find the function that can solve my problem

that would be like

a=running_sum([quit people])

b=lookup(sum([staff every month]),-running_count([staff every month]+1)

c=sum([staff every month])

THEN