Calculation in Expression outside the selection

[#1]

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

[Gysbert_Wassenaar]

See attached qvw

[#3]

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

[jagan]

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.

[Gysbert_Wassenaar]

Then we use applymap instead. See attached updated qvw. Don't forget to delete the original first.