topic Re: Can you help me to solve this tricky problem in QlikView

Can you help me to solve this tricky problem

Fri, 13 Jun 2014 19:22:04 GMT

I have the following data model.

Header 1	Header 2
Skill ID	Employee ID

Header 1	Header 2	Header 3
Employee ID	Month	Remaining Capacity(can be +ve or -ve)

Month Filter
Month

Now I need to display the following:

Skill ID	Skill Count	Remaining Count
Skill ID	Count of employee with this skill	Count of employee having this skill and remaining capacity>0

The user shall have a month filter where he can select a range of months. Hence the above table should display the remaining count, by aggregating for each employee over the given duration of month for the remaining capacity column. Then if this sum is positive the count should be increased by 1 else don't increase.

Can anyone help me how should i acheive this. Thank you in advance.

Re: Can you help me to solve this tricky problem

MK_QSL — Sat, 14 Jun 2014 10:20:24 GMT

Better if you can provide us sample data file. It is very difficult to work without actual data in hand.

Re: Can you help me to solve this tricky problem

swuehl — Sat, 14 Jun 2014 10:28:34 GMT

I agree it would be better to post some more details about your data.

Just guessing:

Create a straight table chart with dimension [Skill ID], then two expressions:

=count(distinct [Employee ID])

or if you want the count independent from your Month Selection:

=count({1} distinct [Employee ID])

=sum( aggr( if(sum([Remaining Capacity]>0,1,0), [Employee ID]))

=count({<[Employee ID] = {"sum([Remaining Capacity])>0"}>} distinct [Employee ID])