4 Replies Latest reply: Sep 4, 2014 5:21 AM by Gysbert Wassenaar

# Calculation in Expression outside the selection

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

ComponentStatusAssigneeProject Key
ABCOpenUser 1111
ABCTestUser 1112
XYZOpenUser 2113
XXXTestUser 1114

Duration:

ComponentStatusDuration(in days)
ABCOpen1
ABCTest2
ABCImplement1
XYZOpen1
XYZTest2
XYZImplement3
XXXOpen1
XXXTest1
XXXImplement1

Capacity:

AssigneeCapacity(in days)
User 12
User 22

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

• ###### Re: Calculation in Expression outside the selection

See attached qvw

• ###### Re: Calculation in Expression outside the selection

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

ComponentStatusAssigneeProject Key
ABCOpenUser 1111
ABCTestUser 1112
XYZOpenUser 2113
XXXTestUser 1114
ABCOpenUser 1115

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

• ###### Re: Calculation in Expression outside the selection

Hi,

Try this script

Projects:

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

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

];

Dimension:Assignee

Expressions:

sum([Duration(in days)])

sum([Capacity(in days)])

Hope this helps you.

Regards,

jagan.

• ###### Re: Re: Calculation in Expression outside the selection

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