1 Reply Latest reply on Jul 2, 2016 3:05 PM by Shinichiro Murakami

    Department Attrition Rate

    Shariq Mohd

      Hi All,

       

      Urgent help . I need to create a band and department attrition % dashboard. To validate the numbers i calculated them in excel. (See sheet "Pivots Combined" in attached excel file ) the attrition rate for department is incorrect though band Attrition rate is correct also filtering data using sales and non sales also shows different numbers i wonder why. All Calculation Logics are given in sheet  "Assignment"

       


      Please help !

       

       

      I am using  tableau 9.1

       

      Regards

      mohd Shariq

        • 1. Re: Department Attrition Rate
          Shinichiro Murakami

          This was quite difficult to understand the data structure.

          As shown above, the Department is not aligned between Jan 1st and March 31st, Then, total count of Employee ID is not correct for Department whichever department field you use..

          While, I have not checked detail, but guess the "Band" is same.

           

           

          The point is that

          Because the department is different from three data set, you can not use "department" across three data point to calculate number of employees.

          What I tried here is that I calculated(counted) employee ID per department @ respective Data set.

          Then, connect the data with linking "Department" as a key.

           

          I think you can restructure how to get the data from other system, but I did with using given data set.

          Anyway, I hope you can understand what should be done thru this concept.

           

          Duplicate (x2) the data source and rename "3 month" , " Jan 1" ,a and "Mar 31".

          Link the data as below table

          Count Employee ID at respective data point

           

          Then on primary data source add create calculated field.

          [HC as on 1st Jan]

          [Exits in 3 months (Tableau-Assignment) Jan1].[Count Emp ID Jan1]

           

          [HC as on 31st March]

          [Exits in 3 months (Tableau-Assignment) Mar31].[Countd EMP ID Mar 31]

          Below two can stay same.

           

          [AVG HC]

          ([HC as on 1st Jan]+[HC as on 31st March])/2

           

          [Attrition Rate]

          [Number of Exits in 3 months]/[AVG HC]

           

           

          Then I got what I believe correct data.

          There are still disconnection from Excel validation number, but I'm afraid excel formula is wrong like below.

          Also, PC Smart Device Count of Employee ID is something different.

          Tableau shows 11,but excel shows 4.

           

          Thanks,

          Shin

          1 of 1 people found this helpful