I don't know why I am having such difficulty with this but I thought I would finally ask for some help as I am sure there is a simple solution. I am struggling with how to use sum(total...) and sum(all...) and aggr so maybe that is the key.
ADJ_HRS = an individual's capacity for the month (hours) from a pool (they could be in multiple pools for a split % of their time)
ALLOC_HRS = # hours an individual is allocated to a project from a pool (they could be in multiple pools for a split % of their time)
ACT_HRS = # of hours an individual booked on their timesheet
MONTH_CAP = Total # of working hours in the month
If the data was all at the same granularity, I would think a simple FTE Capacity Calculation would be sum(ADJ_HRS)/sum(MONTH_CAP). Unfortunately, this is not the case.
How can I get the expression to sum up the selected user's ADJ_CAP across multiple rows while only taking one instance of the MONTH_CAP per month selected? If there are several people selected vs 1, the sum(month_cap) should not change if the time period remains the same.
Without some real data to play around with, I can only make a guess. Perhaps sum(ADJ_HRS)/only(MONTH_CAP) will work. If not, please post an example document that demonstrates the problem.
