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

    How to calculate attrition rate of every month

    GAO Yuanyang

      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 timedepartmentstaff number
      2018/7/1A16
      2018/7/1B24
      2018/7/1C17
      2018/1/1A19
      2018/1/1B21
      2018/1/1C16

       

      QUIT-LIST

      IDdepartmentleave time
      NO.068A2018/1/22
      NO.180A2018/3/21
      NO.181A2018/5/17
      NO.183A2018/1/9
      NO.188B2018/4/28
      NO.204A2018/3/1
      NO.206A2018/3/1
      NO.213A2018/3/1
      NO.008C2018/5/16
      NO.038C2018/6/11
      NO.043C2018/6/27
      NO.058C2018/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
          Yuriy Fal

          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.

           

          Please find the attached.

           

          Yours,

          Yuri

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

            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

              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