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?
provided is dummy data and a working mockup in excel as well as my attempt at replication. the example in excel works as expected, the missing component is a roll up to month (let's ignore partial weeks, my data is actually at daily level anyway)
my qv challenges are:
1. i'd like to see zero/null values plotted, yet after toggling some checkboxes they don't appear to be
2. the summation/all data/no choice seems to be completely incorrectly calculated
3. when i want to see paris office headcount and demand, i don't want to have to choose employee office paris for headcount and additionally choose project office paris in 2 boxes - i just want to choose paris.
sickmint79 wrote:1. i'd like to see zero/null values plotted, yet after toggling some checkboxes they don't appear to be
Check "Show All Values" for your Dimension. You also want to check "Suppress When Value is Null", to avoid null dimensions values.
sickmint79 wrote:2. the summation/all data/no choice seems to be completely incorrectly calculated
I couldn't quite see that happening...
sickmint79 wrote:3. when i want to see paris office headcount and demand, i don't want to have to choose employee office paris for headcount and additionally choose project office paris in 2 boxes - i just want to choose paris.
Well, here you are stepping on a "shaky ground"... When you select "Paris" as location, what number would you like to see as a total work hours - summary of all hours reported for the projects that originated in Paris or the summary of all hours that were reported by the employees from Paris ? Those are two different numbers, and you have to decide which one is "the right one". Same goes for Solutions.
Generally speaking, you can define 2 island fields - Office and Solution, not linked to either one of the tables, and resolve your selections specifically in every expression using Set Analysis. This way, you can select "Paris" for the "island field" and then have one expression summarizing Paris Projects' hours and another expression summarizing Paris Employees summary and even a third expression summarizing Paris employees' hours spent on projects outside of Paris.
Heh, I went and tackled this independently before reading your post. After a lot of poking, I ended up with exactly what you suggested - show all values, suppress when null, office and solution as island fields, and connecting them to the expression via set analysis.
I'm pretty happy with the solution, particularly since we both arrived at the same one. Since it is very questionable ("shaky ground") what we MEAN by the Paris headcount and demand, putting the complete definition into the expression with set analysis instead of building it into our data model seems like a good idea to me.
2. there are only 3 resources, it is impossible for the resource count to exceed 3. for the month view however you see things exceeding 3. at a week level things still look odd, it does not match what i showed in my excel file.
3. not 100% following your island/set analysis, please tell me if this is correct:
rather than applying the business logic at a data modeling level, it is done through expressions. a table of nothing but the distinct offices will be created.
the logic for headcount would then be:
if islandOffice = null count(distinct employee id) else count(distinct employee di) where islandOffice = resourceOffice
the logic for demand would then be:
if islandOffice = null sum(hours)/40 else sum(hours)/40 where islandOffice = projectOffice
the cost of this solution being additional qv complexity, and it probably being slower as this evaluation and branching logic is applied rather than a join.
sickmint79 wrote:there are only 3 resources, it is impossible for the resource count to exceed 3. for the month view however you see things exceeding 3.
The headcount is 3. The demand is higher, as it was summing up the hours worked and dividing by 40. The result is in worked man-weeks. They're comparable at the week level, but different at the month level. Probably multiple ways to solve it. Need help with it or can you tackle it on your own?
sickmint79 wrote:at a week level things still look odd, it does not match what i showed in my excel file.
Every combination looks like an exact match to me, and I just double-checked them all. Which specific combination of office and solution didn't match on your computer? What did you see?
sickmint79 wrote: not 100% following your island/set analysis, please tell me if this is correct:
Looks to me like you followed it just fine. While you could likely implement the business logic in the data model, it seemed better in this specific case to do it through expressions. It just makes it easier to see exactly what's going on, at least to me. I would guess that it is slower than a data modeling solution, but I'm guessing not by much. I believe set analysis expressions are evaluated using QlikView's internal logic just like you had made the selections yourself. So it should be nearly as fast. I could be wrong, though.
i haven't tried playing around with it yet, but thought of something this morning regarding islands too - the island is basically just some variable i'm using. if i stick it out there and the user chooses paris, yes the logic in the chart can take into account the correct values, but i lose the nice effect of having all my other lists affected. for instance, my resources list doesn't fall into only resources working on a paris project. i am wondering if instead of a lonely island by itself, i can connect it, ie. have it contain all combinations of office, office_task, office_employee. with office_task named and office_employee named on their tables respectively. in which case the task will join to tasks and employee to employees correctly - although so as not to create a look i think i will need to load the employee table a second time. the first instance would be hanging off of the task area, the latter be on the new area. if you're able to follow my rambling let me know your thoughts!
While I admit that I didn't really follow your suggestion, I think you're looking at a fundamental problem that can't be solved with mere data modeling and can only be solved on the reporting side.
Let's simplify your example even further to see if that becomes clear. Project A is based in Chicago. Project B is based in Paris. Andy works in Chicago. Bob works in Paris. Both employees work full time on project A in week 1. Both employees work full time on project B in week 2.
You want to select Chicago, and see headcount of 1 both weeks. To do that, the set that you are reporting from CANNOT include Bob. But at the same time, you want to see a demand of 2 in week 1, and 0 in week 2. To do that, the set that you are reporting from MUST include Bob.
See the problem? I solved it by using set analysis to define two DIFFERENT sets that I could use at the same time. One set would include only Andy for both weeks. The other set would include Andy and Bob the first week, and neither of them the second week.
ANY data solution that explicitly uses Chicago to mean both Chicago projects and Chicago employees is going to give you the INTERSECTION of these two data sets. Selecting Chicago would show you only Andy in week 1, as that was the only work done by a Chicago employee on a Chicago project.
You can still back away from this using set analysis to get back to the two different data sets you want. But unless you really do want the intersection of the two for all other reports, I don't think you're going to see what you want.
Or I could be completely misunderstanding what you're trying to do. Or I could simply be wrong. Certainly possible.
