-
1. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 2, 2018 10:14 AM (in response to Caroline McDonald)
Hi Caroline
Understand your data is confidential - that said it would help to see your data, how it is structured and joined
I was tracking with you LOD expressions tied to month or year but
you mentioned the 2 data sources -
the values used in LOD expressions must come from a single data source and depending on how you combined the sources using data from different sources may have other limitations
any chance you can provide some dummy data but include 2 sources?
Jim
-
2. Re: Calculation Help - Attrition Rates
Deepak Rai Feb 2, 2018 10:34 AM (in response to Caroline McDonald)If you would Join your data sources on say Dates. Then using LODs it is possible to get you what you need. Please attach some dummy datasets.
Thanks
Deepak
-
3. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 2, 2018 1:10 PM (in response to Jim Dehner)Hi all,
I've attached a (VERY) simplified data set here. I have several other columns that I'm hoping to be able to filter on as well, so trying to take those into consideration even as I look at the dummy data. Thank you so much for your help!
Caroline
-
Attrition Data.twbx 460.8 KB
-
-
4. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 2, 2018 1:35 PM (in response to Caroline McDonald)
Hi Caroline
Thanks for attaching the file
if the denominator is the issue
2 LOD's
this is monthly
and annually
it will return values like this
did you need added help or can you take it from here
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.
-
5. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 2, 2018 1:50 PM (in response to Caroline McDonald)
I looked at the terminations and have an annual term calc
and a turnover rate of
that added to the chart and removing months to give a summary returns this
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.
-
6. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 2, 2018 2:03 PM (in response to Jim Dehner)Hi Jim -
Thanks so much for the quick response! I'm following what you're doing and I think this is what I was trying to get to - just wasn't sure how to do it. I'll take a look and let you know if I have any additional questions!
Thanks again!
Caroline
-
7. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 2, 2018 2:35 PM (in response to Jim Dehner)Hi Jim,
This is great, but I still have a couple of issues...
1. The number of terms and the turnover rate should continue to grow throughout the year - this is just a fixed number. For example, I might want to know what the YTD turnover rate was in June.
2. When it's not a full year (like 2018), the Average Annual HC number is incorrect - I'm assuming because the LOD was divided by 12. For January of 2018, it should be 604 since this is the only month in the year so far, not 50.3.
Any additional thoughts?
-
8. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 3, 2018 4:12 AM (in response to Caroline McDonald)
Good morning
My Bad thought you were just looking for the annual
here is the monthly running total of terms
running_sum(sum(
{ FIXED year([Actual Termination Date]),MONTH([Actual Termination Date]):
countd([Employee ID])}))
and the monthly rate formula
[mnth attrition-runningtotal]/
(SUM([Sheet1 (Employee Data Set_Dummy Data_Tableau)].[average annual HC]))
these are each table calculations that need to be set as shown
and it will return this
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.
-
Attrition Data_v10.2.twbx 515.3 KB
-
-
9. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 6, 2018 8:24 AM (in response to Jim Dehner)Hi Jim,
Thanks! This is very helpful...I think we're almost there! Unless I'm doing something wrong, I'm still having trouble with the 2018 numbers (question number 2 in my previous post). Any thoughts on that?
Thanks!
Caroline
-
10. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 6, 2018 9:32 AM (in response to Caroline McDonald)
ok then divide by the count of months
returns this
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.
-
Attrition Data_v10.2_v10.2.twbx 512.9 KB
-
-
11. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 6, 2018 10:56 AM (in response to Jim Dehner) -
12. Re: Calculation Help - Attrition Rates
Jim DehnerFeb 6, 2018 11:14 AM (in response to Caroline McDonald)
this is the formula
sum({ FIXED YEAR([Point in Time]): sum([monthly hc])})/[count of months]
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.
-
13. Re: Calculation Help - Attrition Rates
Caroline McDonald Feb 6, 2018 11:17 AM (in response to Jim Dehner)Great! Thanks so much for all of your help!!!