Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.,
I have a problem where I want to show the allocation of resource based on the project they are assigned to
My dataset contains a list of project, component, status and assignee
I have also got another sheet which breakdown the timing for each component per status
Another sheet has total availability for assignee/resource
Ex:
Projects
Component | Status | Assignee | Project Key |
---|---|---|---|
ABC | Open | User 1 | 111 |
ABC | Test | User 1 | 112 |
XYZ | Open | User 2 | 113 |
XXX | Test | User 1 | 114 |
Duration:
Component | Status | Duration(in days) |
---|---|---|
ABC | Open | 1 |
ABC | Test | 2 |
ABC | Implement | 1 |
XYZ | Open | 1 |
XYZ | Test | 2 |
XYZ | Implement | 3 |
XXX | Open | 1 |
XXX | Test | 1 |
XXX | Implement | 1 |
Capacity:
Assignee | Capacity(in days) |
---|---|
User 1 | 2 |
User 2 | 2 |
Based on the above dataset, I want to show a bar chart per user -
User 1 -
Capacity:2
Allocation:
Component: ABC - Status: Open count 1 = Duration =1 --> 1*1=1 day
Component: ABC - Status: Test count 1 = Duration 2 --> 1*2 = 2 days
Component: XYZ - Status: Open count 1 = Duration 1--> 1*1 = 1 day
So total capacity = 2 and Allocation = 4
The bar chart should have these two as expression
See attached qvw
Hi Gysbert,
Thanks for your answer, it worked for the example given above however I didn't quite gave full data, the problem arises when user has multiple projects for the same component for same status
i.e.
Projects
Component | Status | Assignee | Project Key |
---|---|---|---|
ABC | Open | User 1 | 111 |
ABC | Test | User 1 | 112 |
XYZ | Open | User 2 | 113 |
XXX | Test | User 1 | 114 |
ABC | Open | User 1 | 115 |
Now I have 2 projects of ABC component in Open status so I need to know the count of Open i.e. 2 and multiply it by
duration i.e. 1
So for User 1 capacity will be 2 and allocation will be
ABC Open x 2 = 2 x1 = 2
+
ABC Test x 1 = 1 x 2 = 2
+
XXX Test x 1 = 1 x 1 = 1
Total = 5
In your example if I sum(capacity) = 2 and allocation: sum(Duration) = 4
Hi,
Try this script
Projects:
LOAD * INLINE [
Component, Status, Assignee, Project Key
ABC, Open, User 1, 111
ABC, Open, User 1, 115
ABC, Test, User 1, 112
XYZ, Open, User 2, 113
XXX, Test, User 1, 114
];
LEFT JOIN (Projects) // Joined to tables
LOAD * INLINE [
Component, Status, Duration(in days)
ABC, Open, 1
ABC, Test, 2
ABC, Implement, 1
XYZ, Open, 1
XYZ, Test, 2
XYZ, Implement, 3
XXX, Open, 1
XXX, Test, 1
XXX, Implement, 1
];
Capacity:
LOAD * INLINE [
Assignee, Capacity(in days)
User 1, 2
User 2, 2
];
Dimension:Assignee
Expressions:
sum([Duration(in days)])
sum([Capacity(in days)])
Hope this helps you.
Regards,
jagan.
Then we use applymap instead. See attached updated qvw. Don't forget to delete the original first.