1 of 1 people found this helpful
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.
Please find the attached.
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
I study the function in the attchement . I find the function that can solve my problem
that would be like
b=lookup(sum([staff every month]),-running_count([staff every month]+1)
c=sum([staff every month])
adding attrition rate =a/(b+c)*2
and it shoud be table down or pan(down)
HOPE my example can help other people