2 Replies Latest reply on Jun 3, 2016 5:08 AM by Dileep Reddy

    Attrition % in Tableau

    Dileep Reddy
    Dileep Reddy Jun 3, 2016 5:08 AM

      Hi,

       

      I need to calculate Attrition% in tableau for one of our dashboards. Data has 4 columns : Fiscal Year, Posting Period, Active Headcount, Terminations.

       

      I have 2 parameters set up one each for Fiscal Year(2015, 2016) and Period(1 to 13). Each Fiscal year has 13 periods unlike 12 calendar months

       

      So If want to display the Attrition % for say Period 11 and 2016 Fiscal Year. Calculation should go like below.

       

      Attrition % = (Last 13 periods Terminations w.r.t period selected) / (Avg. of last 14 period Active headcount w.r.t period selected)

       

      When I say Last 13 periods, it should calculate the the period selected in the parameter. In our example, for Period 11, 2016, last 13 periods would start from Period 12, 2015 to Period 11 2016.

      Same logic goes for last 14 periods for Active headcount.

       

      Eg: If I want calculate the Attrition for Period 11, 2016, the Calculation goes like this.

       

      Last 13 periods Terminations w.r.t period selected would be 84 as shown below

       

      last 14 period Active headcount w.r.t period selected would be 772 as shown below

       

      So Attrition% =  84 / (772/14) = 152.3%.

       

      This is just a sample example.

       

      The reason for duplicate posting periods may be because of different regions/departments. But when calculating total attrition we need take the sum of all values with falls under last 13 periods range.

       

      My need was to create a calculated field which gives me Attrition % based on the selections in the 2 parameters. (period and Fiscal year)

       

      Also I need to plot a graph showing trend of last 13 periods attrition %.

       

      Can any here help me with this calculation. Let me know if you need any further information

       

      I have attached the sample data.

       

      Thanks,

      Dileep

       

      Message was edited by: Dileep Reddy

        • 1. Re: Attrition % in Tableau
          Ashish Chaudhari
          Ashish Chaudhari Jun 3, 2016 4:24 AM (in response to Dileep Reddy)

          Hi Dileep,

           

          Let me know why such a double entry exists. are this period of posting is equivalent to months. I think entry with the 13 posting period should also be deleted if that's the date. Because If you select any period we can do dateadd('month',-13,period). Thus there is no need to create separate provision in the data itself.

          Let me know if you have queries. Please confirm the question again so that ppl can proceed on the same.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Attrition % in Tableau
            Dileep Reddy
            Dileep Reddy Jun 3, 2016 5:08 AM (in response to Ashish Chaudhari)

            Hi,

             

             

            First of all, period is not a month..In a calendar year we have 12 months, where as here in our organization we have 13 periods and each period contains 4 weeks.

             

             

            Coming to the duplicate posting periods, we will have duplicate values because those of different departments. The ones that you have highlighted may be for different departments. If I want to calculate Total Attrition% I need to sum all those duplicate periods.

             

             

            I have edited my question by giving an example.

             

             

            Let me know if you need any additional information.

             

             

            Thanks,

            Dileep