
1. Re: How to calculate attrition rate of every month
Yuriy Fal Aug 10, 2018 9:31 AM (in response to GAO Yuanyang)1 of 1 people found this helpfulHi 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 HRrecordmoremonth 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.
Please find the attached.
Yours,
Yuri

2. Re: How to calculate attrition rate of every month
GAO Yuanyang Aug 13, 2018 3:55 AM (in response to Yuriy Fal)Thank you very much.
THIS is a really wonderful join for me and I will never work out with out your help.
I WONDER ANOTHER question about how to calculate "add up attrition"
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
GAO Yuanyang Aug 19, 2018 8:55 PM (in response to GAO Yuanyang)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
adding attrition rate =a/(b+c)*2
and it shoud be table down or pan(down)
HOPE my example can help other people