Calculating total available time based on number of resources (including resource's available time)
Hi all,
I have my transaction table linked with my monthly periods table. One resources (resource A), for example only has transactionsin the first half of this year.
The available time (networkdays) from the beginning of the year until our september month-end is 200 days.
Resource A's network days are 135.
If I want to calculate the total available network days between Resource A and another resource B who is still employed and booking time, how do I do that?
Initially, before I discovered this problem, I was simplay using the calculation:
If I then select my date range, it obviously just multiplies the number of resources (2) by the overall period range, rather than each resource's available range.
does that make sense?
If so, can anybody suggest a way to do this please?
If i understand correctly, you want to disregard the selection on date, but only for START_DATE. =COUNT(DISTINCT RESOURCE_CODE) * NETWORKDAYS(MIN({$<START_DATE=>} START_DATE), MAX(END_DATE))
You find more in the Set Analysis help: sum( {$<Region = >} Sales ) returns the sales for the current selection, but with the selection in "Region" removed.
If I select the whole of 2008 for example, and a resource joined at the beginning of April and left at the end of May, I would want to show their availabiliy based on just those two months, and every other resource based on their individual MIN(START_DATE) and MAX(END_DATE)
I have uploaded my qvw so you can see the table structure.
Have filtered on just a handful of resources. I
have included some text fields to illustrate what the problem is with the values I expect to see.
John said the right thing. It all depends on the resource.
You say above those 19000 that "This figure needs to be the same as the total in the table above".
It need not, from what i've seen.
Because, in those textboxes you have: - total nr of resources in the period you selected, - maximum number of networkdays, not taking into account the resource (that's meaning min of date of any resource, max of date of any resource). - and, in the third textbox, you put a product of these two.
Let's take an simplified example. Me and you. I've worked from 01/01/2009 till 02/01/2009. And you've worked from 30/01/2009 till 31/01/2009. Total number of resources: 2. Total number of days: 31 (min=01/01/2009, max=31/01/2009). The product for them: 62.
It isn't correct that way. It must be put into relation with resource. Min and Max of date of that resource, not by total.
Isn't it?
Moving over this, a problem appears when you select only one year, and one month. Logicaly, saying "I want to see networkdays for this year, by the end of that month (start of year to end of that month)". Here, you must diregard selections in PERIOD_MONTH for the START_DATE.
My understanding was that net work days calculated from the min and max date of the resource IS what is shown "in the table above". We want to calculate the net work days for each resource, then sum them up. That's what the sum(aggr()) does for us. Now, if the net work days in the table are calculated incorrectly, that's a separate issue. I didn't validate the data model or the calculation of net work days per person. And looking at the data model, yeah, it looks like it might be wrong. But you would still get the total the same way, sum(aggr(corrected calculation,RESOURCE CODE)).
