I've been struggling with the idea of what I am trying to acheive for some time.
I'm producing some resource capacity dashbords based on our data from Clarity PPM
In this system, there are projects with 1 or more tasks.
each task will have one or more resources assigned.
That resource then books time (ACTUALS) to the assignment (task) and also has forward looking ETC's on that project.
Aside from the project/task/assignment each resource has a general availability.
ACTUALS, ETCs and Availability are all time-sliced into the same tables
In my qvw, you can see the script has a loop occuring.
I'm reading in the time-sliced data twice. once with actuals/ETCs against time and once with resource availability.
I need them governed by a single data picker, so I can see on a particular date, how many ETCs a resource has (thats the planned schedule) and what their availability is, so a simple sum will show their spare capacity.
I'm not sure how to construct the query so that I can acheive this.
I need the two time slice areas separate from each other, but linked by date.
Thanks in advance for any assistance you might be able to offer.
1) create 2 differents facts concatenated, each with a field of type date. Selecting a date will select the first fact with the first measure, and the second with the second measure.
2) create a matrix that link each fact to 2 dates, the date of first dimension and the date of second dimension. So if you select a date, it will select all lines of fact where the date is in first dimension OR in second dimension.
3) in the chart, use the date in Set analysis, with dimension1 = {"Date"} for the first measure and dimension2 = {"Date"} for the second measure.
Not quite sure I have understood correctly, but how about concatenating the 'AVAILABILITY' and 'ACTS_ETCS' tables together (forced using CONCATENATE LOAD)?
You could use a flag to differentiate bewteen the two record types in the table so you can work with one set or the other in expressions etc.
I might be tempted to join the ACTS_ETCS table with the Task_Assignment table first and then concatenate the AVAILABILITY table. This would null out any association between Project and Availability but, this would appear to be logically correct.
Have joined (left join) on the ACTS_ETCS (Actuals and ETCs)
then concatenated the availability - so they share common SLICE_DATE and HOURS values..
Doesnt behave how I need..
The end result i'm looking for is to be able to show a graph of a SUM of AVAILABILITY on any particular week or month, and then show the SUM of ETC i the same graph so I can see the available capacity (availability-ETC)
Likewise, if I choose a project, I would like to see if the ETC of the project for any particular month is greater than the total available time of the resource.
This means I would definitely need to link the availability (key field is resource id) to the task_assignments table - but with a single date value linking them...
somehow..
going around in circles here (much like the circular reference in my original attached qvw lol)
I have run this into my script and, yes I can compare the both together, but the moment I select a project for example, it obviously filters out the availability.
I was wondering if there is a way I can do set analysis of some description so that if I select a PROJECTID,
my graph (bar chart) sums up the availability disregarding that selection.
I would want the ACTUALS to behave 'normally', but availability to show without the selection on project.
Does this make sense?
The same goes for selection by resource - I would want to be able to pick a single resource, and see their total ACTUALS by time, and their availability.
Yeah, I mentioned that further up - project selection will exclude the availability and that is logical because selecting a project will not have an association with the availability.
However, you can insert a Set in the calculation of the availability that will ignore selection on ProjectID (and any other Project fields that might be on the screen) like this:
