7 Replies Latest reply on Jan 23, 2018 1:10 PM by Darenne Poyser Branched to a new discussion.

# Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Hi,

I'm trying build the metric to calculate the Monthly Employees Attrition Rate (Monthly Turnover Rate), by using the attached sample of raw data.

The definition of the Metric is  "In any given month, divide the [number of employees who left the company] during that month   by the   [number of employees who were in the company] at the beginning of that month

Now, by looking at the attached  .twbx workbook and at the excel file (for checking the numbers), I realized that Tableau does not give me the correct number when it comes to the RUNNIG_SUM of count of Entry Dates, i.e.:

- over the leaving month "April 2012" it should be equal to 7 but it is displayed as 8.

- over the leaving month "May 2013"; it should be equal to 8 but it is displayed as 9.

Hence the final calculation is impacted.

Can someone please look at this and provide some feedback/input?

Em.

• ###### 1. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Em, thanks for posting both a sample workbook and the Excel file. So the issue you're having is you are using Leave dates on the column shelf, but then running count the Entry dates. When you uncheck Analysis\Aggregate Measures you get this view:

By using the Leave date the Entry dates that don't match a Leave date produce a Null value. These get counted first then T moves onto the months that have a match. You can see that the 6/5/2012 shouldn't be in the first quarter, and this is what is throwing the count off by one.

In the attached workbook I switched from using Leave to using Entry and this gives a correct count for Entry. But you have other problems. You are using a date part instead of a date value:

So you're essentially telling Tableau to ignore the Year and only aggregate on the month. If this is really what you want to look at then just turn on Show Missing values and you'll you're good to go. You counts will look like this:

If that's not what you're looking for, then one way to attack this sort of problem is to reshape your data either at the data source or in a custom SQL connection. In the attached file I reshaped the data so it looks like this:

Let me know if you have questions.

--Shawn

• ###### 2. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

This is awesome Shawn! thank you very much for your help and the comprehensive explanation!

I will have to reshape the data, then, as you suggested.

If possible, would you please explain me (or point me to the doc) how I can add a subTable to my data by using a custom SQL?

As a preference I would let Tableau do the reshape for me rather than doing it at the source.

Thanks again!

Em.

P.S.For the sake of the completeness, I just want to rewrite here the formula I'm using for the attrition rate (where the denominator is the average of actual employees in the company during the given month)

Attr. Rate = [Sum of LEAVE]  /  Denominator

Denominator = ((

LOOKUP(RUNNING_SUM([Sum of ENTRYs]),-1) - LOOKUP(RUNNING_SUM([Sum of LEAVEs]), -1) +

(RUNNING_SUM([Sum of ENTRYs]) - RUNNING_SUM([Sum of LEAVEs])) ) / 2

• ###### 3. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Lets see, it would be something like this:

SELECT [Personal ID Number]

, [Entry Date] AS Date

, "Entry" AS Type

FROM MyTable

UNION ALL

SELECT [Personal ID Number]

, [Leaving Date] AS Date

, "Leaving" AS Type

FROM MyTable

This will basically reshape your to look like the Excel file I put together for you. Is this what you were looking for?

--Shawn

• ###### 4. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Not really....

What I am looking for is to know how to write SQL in Tableau? Where to click...

Thanks again!

• ###### 5. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

What version are you running? Is your data in Excel?

--Shawn

• ###### 6. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Hi

I'm using the latest in trial.

Yes data are in Excel

Thank you

• ###### 7. Re: Bug in RUNNING_SUM ? - Attrition Rate (Turnover rate)

Hi there:

This was a super thorough explanation!

Unfortunately, I'm stuck as well, even having read through so many of the various community/other site forums.

I'm trying to figure out how to restructure the data as you have with the Term/Hire dates all in one column with their Type in the next column. When I do it, it creates a huge number of NULLS and seems to throw off the data completely.