I have a problem I am trying to solve that I need help with. Within my dataset I have two different types of time - forecast time and available time - both tied to employees. I also am aggregating employees into two different versions of practice - one based on project role which is the basis of forecast time and the other which is based on cost center which is the basis for available time. The values for Practice are the same in both versions
I want to be able to divide forecast time by practice (based on project role) by available time by practice (based on cost center). In pivot charts in Qlik I can see the correct data if I roll up forecast by practice and available by practice but I cannot get the formula to recognize the different versions of practice - for example in my first version of this where employee linked the time types together, it would always divide by practice based on project role.
I have created a second version where I have separated employee into two different and unlinked fields - employee on project and employee in cost center but still cannot get the formula to work. This also limits me in other ways as I would still like to be able to show the forecast / available at an employee level which I can't do when separated like this.
Does anyone have any suggestions for how to handle a situation like this where I want to reflect two different aggregations of what is essentially the same field into a calculation?
I have attached two files showing data - one from the version of the app where i have kept employee ID linked and one from the version where they are separated. I will focus my reply on the one where they are linked.
On the left had chart you see Practice as determined by cost center. In the example you see the 4 employees in our exec sponsor cost center and their correctly calculated available hours, monthly average available hours and UTE rate. The totals at the practice level are also accurate for these. The forecast revenue hours are accurate at an individual level but not in total here
On the right hand table you see Practice rolled up based on project role. You can see there are more people who hold that project role than there are in the cost center. Once again the values at an employee level are all accurate.
I want to be able to do 2 things:
1) divide the forecast hours by the monthly average available hours at an employee level - this is no issue and works perfectly
2) Divide the forecast hours at a practice level based on project role - 393 from the right hand table - by the monthlly average available hours based on cost center - the 72 from the left hand table and represent that in a rollup based on practice based on cost center from the right hand table. Essentially I want to calculate how many FTE's I need to deliver the forecast based on the employee population in the cost center and then compare that to the actual employees- also based on cost center so the 4 in the left hand table.
Currently there are not huge differences between the two rollups but as you can see there are some and they could get material at some point so really hoping I can work this out.
