newbie help - trying to calculate headcount vs. project demand
this may have a similiar solution to another question i posted as they may be along the same vein...
data:
i have employees, each with data assigned about them. office and solution. they work on projects, each project has its own office and solution. a resource may be borrowed to work on a project that is not within their own office or solution.
i want to graph the project demand. number of hours charged to a project each month divided by number of work hours in the month. i want to be able to drill down to weeks with a group object. i want to have selectors for project office and project solution. this is what i have currently in place.
now here's the tricky part - i also want to graph the headcount of employees within the same office or solution. when i hit Paris in the office, i want to see Paris project office demand, as well as Paris headcount. not sure if it matters but a resource could be on multiple projects by the way.
right now i am graphing hc as count(distinct employee id) - with nothing selected this just seems very high. when i do select something the numbers seem to be closer to reality - however the hc i'm getting does not appear to be the headcount of employees within the employee office, but rather the count of employees working on a project in that project office used in the selection.
1. how do i do what i'm trying to do?
2. is there a solution that will apply this logic to both levels of the group i'm working in, months and weeks?
sorry let me just take a step back and respond to this post then, this is back to paris/chicago as an island. am i correct in understanding that this would come with the following costs:
1. every metric would itself have to check the city, as no join is taking place
2. a selector list of resources would no longer be filtered on paris only resources when a paris office is chosen, as it is on an island and no join is taking place
Yes, you're correct. Still, I'm not sure I'd call them costs. After all, if you DO connect the city to both the employee and project, then you get the intersection of the two. That doesn't seem any more useful to you than no filtering at all. But maybe you'd prefer getting the intersection. I believe the same set analysis expressions for headcount and demand would work either way. And then I guess we'd be back to needing to get rid of the loop.
i'll post up a qv file when i have some time with a real example we can play with, i'll give you my pie in the sky and we can try and go from there. from my understanding currently the pie in the sky view is going to remain up there! 😛
