7 Replies Latest reply on May 26, 2016 12:49 PM by Ben Page

# Calculating Turnover Ratio's

Hello all!  I am trying to create a custom calculation that will give me turnover ratio.  This is something I currently do in Excel (will be connecting the data source so I don't have to refresh manually), but would like to have it done in Tableau so I can automate the data source & graph it. I'm struggling with creating the custom calculation so I can graph the results (I can get averages using the system generated calculations, but that doesn't allow me to calculate the ratio, which is why I think I need a custom calc).  Essentially I need to take the YTD total terms ([FTE_Term]) divided by the YTD average of the total active FTE's ([FTE_Active]) across a time dimension.  There are other dimensions that I will use to create additional detail naturally (such as term reason). I can't figure out the syntax to create a calculated measure so I can graph it and add detail. For example, using the dataset below, I would want to break out that 4.2% ratio and provide the ratio by term reason, or term type, and department, etc monthly or year over year.  Any help would be greatly appreciated!

Example Dataset:

Active:

Jan    Feb     Mar     AVG

504     531     542     526

Terms:

Jan     Feb     Mar     YTD

8          7          7        22

Ratio:

YTD Mar

22/526=4.2%

• ###### 1. Re: Calculating Turnover Ratio's

Hi,

I was able to get the result with one calculated field. I basically leverage a running sum for your YTD totals and a Running Average for the Active totals.

Let me know if this helps,

Ben

• ###### 2. Re: Calculating Turnover Ratio's

Thank you Ben, that worked perfectly!  Until I had to drill down with additional dimensions.  Could be that 'compute as' command, or it could be that it's no longer dividing the total YTD average active for each year...see below. Thoughts?

• ###### 3. Re: Calculating Turnover Ratio's

Yep, looks like you'll need an advanced table calculation that takes into account term status and employment bucket. I could take a look if you attached a packaged workbook.

• ###### 4. Re: Calculating Turnover Ratio's

Thanks Ben.  I'm attaching the workbook.  Another thing to note, there are two employment buckets that I have to consider (which I just realized last night) - the active employment bucket & the term employment bucket. I am not having success in merging those two calculated fields together, but I think that might be part of the problem as the picture above was only the active buckets.  Hopefully whatever magic you are able to create for me will include the ability to add additional dimensions (such as the other db in this workbook, requirements are to be able to filter by executive, function and department, at a minimum).  I would focus on worksheet 15 as that has the data dump as the data source, which is the revised data source that I will be using going forward. THANK YOU!!!

• ###### 5. Re: Calculating Turnover Ratio's

Hi Ben!  Any chance you had an opportunity to look at this?  I appreciate any help you can provide!!!

• ###### 6. Re: Calculating Turnover Ratio's

Ben Page  (sorry Ben, replied to myself in error!  Have you had an opportunity to review?)

• ###### 7. Re: Calculating Turnover Ratio's

Hi Jennifer,

Sorry for the delay, I haven't had a chance to review yet. I will try to get to this when I have some time.

Ben