topic Calculation in Expression outside the selection in QlikView

Calculation in Expression outside the selection

Wed, 03 Sep 2014 06:11:34 GMT

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

Re: Calculation in Expression outside the selection

Gysbert_Wassenaar — Wed, 03 Sep 2014 09:40:46 GMT

See attached qvw

Re: Calculation in Expression outside the selection

Thu, 04 Sep 2014 00:23:42 GMT

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

Re: Calculation in Expression outside the selection

jagan — Thu, 04 Sep 2014 01:44:55 GMT

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.

Re: Re: Calculation in Expression outside the selection

Gysbert_Wassenaar — Thu, 04 Sep 2014 09:21:10 GMT

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