Hello,
I built a table with columns to count the employees. The columns are either Active or Term and if they are Active it has a 1, if not, 0. Same goes for Terms – 1 is they termed and 0 if not. It also has the current effective date.
Then I run a loop that builds a fact table with all the employees in each of the months for a year. So the table looks (something) like this:
RowKeyEmpKeyDeptKeyActiveTermDate
110751020061001
2111001020061001
31392 1 020061001
416921020061001
Then the next month:
RowKeyEmpKeyDeptKeyActiveTermDate
510751020061101
611100 1 020061101
713920120061101
816921020061101
This way I can keep track of who is the active employees each month as well as who terminated that month.and do year to date totals on the terminations, which I need for turnover calculations.
The issue is, when I view the data in a Reporting Services report, and I drill-down to a Department and look who is active, I also see the termed employees. My assumption is because they are part of the count – that part being zero. Is there a better way to approach this?
My guess was not having Active and Terms columns. Instead I thought of a single column with a StatusKey. But if I did that I wouldn’t know how to do the calculations of Year-to-Date terminations.
Any suggestion is greatly appreciated. As well as (constructive) criticism on this technique.
Thank you.
-Gumbatman
I don't see how using YTD can return correct results, because you would end up calculating the same employee as many times as many days he terminated. If you have Active and Term as measures, I'd replays 0 with NULL (there is a property of measure or measure group that says preserve null, I don't remember it's name from the top of my head). Again, if I remember correctly Reporting Services applyes Non Empty to the query, therefore in this case you wan't see employees that have NULL as active, if you use both Employess and Active in the query and you are not drilling down the time. As for calculating number of terminated employes I would use something like this:
count(filter(NonEmpty (employee.members, Term * Time.<today>), IsEmpty ((employe.currentmember, Term, Time.<CurrentYear>.firstchild.firstchild.firstchild)))) //calculates the number of employees that are marked as terminated today, but where still working on january first. Of course if you have laxuary to delete employees from the system that are gone more then a year, you can simplify this formula.
|||
Irina,
Thank you for the information.
What is strange (and I have to look at more closely) is that the YTD terms are calculating correctly even though I am probably double-counting them. I think I only added those who terminated in that year and I added them only to the last month of the year. I have to check that.
What I am still not too clear on is if I drill-down to a Product, will I see the terms and actives who are in that Product when all I want to see is the terms? Will the null, in the terms column, help me with that?
What about combining the Actives and Terms into a single dimension, it is sort of no longer a measure I guess?
Thanks for the help
