2 Replies Latest reply on Sep 1, 2017 4:16 PM by Jim Dehner

    Sales Rep Cumulative Attrition Calculation

    Ian Ellingson

      I am trying to achieve the same result as I currently have in Excel, initially I am looking for simply a table however that may evolve once I have the calculations straight.  Example of desired output below.

       

      Attrition Calc.PNG

        • 1. Re: Sales Rep Cumulative Attrition Calculation
          Matt Lutton

          Any detail you can provide about the mockup and your desired results may help.  For instance, what is the underlying data and what is being calculated (or what should be calculated in Tableau).  I've taken a look, but am not clear on what you are hoping to achieve based on the Excel screenshot alone -- any detail you can provide to help guide volunteers on this Forum may help. 

           

          Best of luck!

          • 2. Re: Sales Rep Cumulative Attrition Calculation
            Jim Dehner

            Hi Ian

            This has had me stumped for about 3 days - but I found an old post and worked from there

             

            The process here is to create a running date that can be used as an axis for your charts -

            The axis starts at you minimum start date and extends past your last end date

            To get the total axis I made a couple of changes in your data set - first I added a record with a start date out late this year

            I placed it in Nashville (my home town) so it can easily be hidden -

            Next I had to deal with the nulls in the end date - I just added a calculation to give them and end date of 12/31 - could be any date in the far future      IFNULL([End Date],DATE('2017-12-31'))

             

            I did not extend the calculations past getting the rep count and attrition and for some reason I am 1 rep off throughout ?

             

            Now this is what the chart form looks

             

             

            and i graph form

             

            Getting there requires some work

             

            First create a start date = datetrunc('month',WINDOW_MIN(min([S-Date])))

             

            And a running date calculation = dateadd('quarter',index(),[Start Date])

             

            Each are table calculations - the start date is set as show on the left and the running date on the right

             

            Then Create a calculation to determine which are active and set an indicator to 1

                      if [Running Date] >= window_Max((max([S-Date])) )

                      and [Running Date]<=WINDOW_MAX(max([cleaned date]))

                      Then 1 else 0 end

            This formula was counter intuitive and had me going for a couple of days - it to is a table calc set as

            Note the placement of the metrics and the restart is critical to this process

            Finally the number of reps is determined          if index() = 1 then window_sum([isactive]) end  - this one was a sticking point-

            finally I realized that the index in this formula in not the same as the index in the running date formula

             

            it to is a table calc set as shown

             

             

             

            Now one last formula - attrition      [Number of reps (copy)]-LOOKUP([Number of reps (copy)],-1)

            and yes it too is a table calculation

             

            In creating the viz place your data set Start Date (I changed the name to S Date while working with the viz) on the column

            Right click it and select Show Missing Values (Critical step)

            Next load up the detail tile as shown above and in the attached - - you will have to set all the table calculation settings after you load the detail tile

             

             

            Ok this was the hard part - the remaining calculations should be much more straight forward

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful