I need to calculate Attrition% in tableau for one of our dashboards. Data has 4 columns : Fiscal Year, Posting Period, Active Headcount, Terminations.
I have 2 parameters set up one each for Fiscal Year(2015, 2016) and Period(1 to 13). Each Fiscal year has 13 periods unlike 12 calendar months
So If want to display the Attrition % for say Period 11 and 2016 Fiscal Year. Calculation should go like below.
Attrition % = (Last 13 periods Terminations w.r.t period selected) / (Avg. of last 14 period Active headcount w.r.t period selected)
When I say Last 13 periods, it should calculate the the period selected in the parameter. In our example, for Period 11, 2016, last 13 periods would start from Period 12, 2015 to Period 11 2016.
Same logic goes for last 14 periods for Active headcount.
Eg: If I want calculate the Attrition for Period 11, 2016, the Calculation goes like this.
Last 13 periods Terminations w.r.t period selected would be 84 as shown below
last 14 period Active headcount w.r.t period selected would be 772 as shown below
So Attrition% = 84 / (772/14) = 152.3%.
This is just a sample example.
The reason for duplicate posting periods may be because of different regions/departments. But when calculating total attrition we need take the sum of all values with falls under last 13 periods range.
My need was to create a calculated field which gives me Attrition % based on the selections in the 2 parameters. (period and Fiscal year)
Also I need to plot a graph showing trend of last 13 periods attrition %.
Can any here help me with this calculation. Let me know if you need any further information
I have attached the sample data.
Message was edited by: Dileep Reddy
Sample data.xlsx 9.3 KB