14 Replies Latest reply on Mar 31, 2020 8:05 AM by swaroop.gantela Branched to a new discussion.

# Calculating Attrition Rate w/a Variable Period

Hi Everyone,

I have a data source that contains 'Date Hired' & 'Date Terminated' as well as a hierarchy.   I'm having trouble when it comes to creating a calculation that works for more than a one month period.

Calculation we are using for Attrition:

# of terminations during the period / (# Active employees at start of period + # of employees hired during the period)

Below is a snapshot of our calculation in Tableau working as intended for a 1 month period of time. Below is what happens when we select multiple periods of time.  The issue is the 'Starting Headcount' aggregates all the periods of time instead of taking the 'Starting Headcount' for the initial period selected.  It's also aggregating the 'Ending Headcount' instead of giving the actual 'Ending Headcount' desired.

The desired 'Attrition %' for this calculation should be 22.85%.  402/(1484 + 322) Ultimately, I'd need to be able to create the proper calculation with any period of time selected, let it be 2 months, 1 quarter or 1 year's worth of time.  I've done some research and think I need to use a LOD expression but I'm unsure of how to write the calculation.

I've attached a sample of my data source.

Any and all help will be greatly appreciated.

Regards,

Charlie

• ###### 1. Re: Calculating Attrition Rate w/a Variable Period

You need to work with parameters here. LODs are not required.

Here is the result of the solution with parameters: In this example:

The starting balance is the number of active employees as of 28-Feb-2019.

The hired/terminated in selected period considers the number of months (3 in this case and corresponds to Mar, Apr and May 2019).

The closing balance is the number of active employees as of 31-May-2019.

Here are the calculated fields created:

Selected Start Date: DATE(MAKEDATE([Year Parameter],[Month Parameter],1))

Selected End Date: DATE(DATEADD("month",[Number of Months],MAKEDATE([Year Parameter],[Month Parameter],1))-1)

Starting Balance: IF [Action Month]<[Selected Start Date] THEN [Hire Status Count]-[Term Status Count] ELSE 0 END

Hired in Selected Period:

IF [Action Month]>=[Selected Start Date]

AND [Action Month]<=[Selected End Date]

THEN [Hire Status Count] ELSE 0 END

Terminated in Selected Period:

IF [Action Month]>=[Selected Start Date]

AND [Action Month]<=[Selected End Date]

THEN [Term Status Count] ELSE 0 END

Closing Balance: [Starting Balance]+[Hired in Selected Period]-[Terminated in Selected Period]

Attrition Rate for Selected Period:

SUM([Terminated in Selected Period])

/

( SUM([Starting Balance]) + SUM([Hired in Selected Period]) )

Hope this helps. A 2019.2 workbook is attached for reference.

1 of 1 people found this helpful
• ###### 2. Re: Calculating Attrition Rate w/a Variable Period

Hari,

Thank you so much for your detailed response.  This is exactly what we were looking to accomplish.  I can't quantify our gratitude.

Thanks again,

Charlie

• ###### 3. Re: Calculating Attrition Rate w/a Variable Period

You are welcome.

1 of 1 people found this helpful
• ###### 4. Re: Calculating Attrition Rate w/a Variable Period

Is there a way to do the calculation that uses MAKEDATE with something else?  MAKEDATE does not work for me.  Thanks.

• ###### 5. Re: Calculating Attrition Rate w/a Variable Period

are you connecting live to your database/file? If yes, can you extract your data and try?

• ###### 6. Re: Calculating Attrition Rate w/a Variable Period

It is a live connection.  I understand that MAKEDATE doesn't work with live data and was wondering if you could show how to perform the same calcs with something other than MAKEDATE.  Thanks in advance for your help.

• ###### 8. Re: Calculating Attrition Rate w/a Variable Period

Thank you for your quick response.  Is this for the start date or the end date?  I am looking for both.  Thanks again for your help!

• ###### 9. Re: Calculating Attrition Rate w/a Variable Period

How did you create the "Action Month" fields for the Hire and term dates? Thank you!

• ###### 10. Re: Calculating Attrition Rate w/a Variable Period

Sorry, I overlooked this and didn't respond. Did you figure out what you needed?

• ###### 11. Re: Calculating Attrition Rate w/a Variable Period

No problem.  I was just wondering if you could provide the calc for start date and end date.  Thanks.

• ###### 12. Re: Calculating Attrition Rate w/a Variable Period

The formula was given in the response. Additionally, the packaged workbook was attached to the thread. You will be able to view it by clicking on the title of this thread and navigating to the specific response. • ###### 13. Re: Calculating Attrition Rate w/a Variable Period

I was asking if the formula you gave as an alternate to using the MAKEDATE function was for the start date or the end date.  I was unclear which it was for and was asking if you could provide an example for start and end date.  The MAKEDATE function will not work as I am using live data.

• ###### 14. Re: Calculating Attrition Rate w/a Variable Period

Randy,

I'm not sure if this will work for your setup,

but there is an alternative to MAKEDATE which uses DATEADD to a date in the past: