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

# Sales Rep Cumulative Attrition Calculation

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.

• ###### 1. Re: Sales Rep Cumulative Attrition Calculation

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

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