topic Want to count based on a sum expression field? in QlikView

Want to count based on a sum expression field?

Mon, 26 Mar 2012 12:18:48 GMT

Hi,

I have a bit of an issue that I'm sure someone can help with! I have a pivot table that looks something like this

User Clients This month Target

User A 50 10

User B 0 5

User C 120 12

User D 75 0

I want to show in a separate place a count of all users who have clients that have no target set for this month. So in the example above, the total would be 2 (User A and C).The "this month Target" field is a sum against a set of all targets that looks at the targets set for users just for this current month. So effectively I want to do a count where this sum is 0 that also has some clients set as well.

I can't find a way of doing this as the count and sum won't work together. Sure it's an aggregation I need, just can't seem to get it to work.

Any ideas very much welcome!

Many Thanks

Nick

Want to count based on a sum expression field?

sivarajs — Mon, 26 Mar 2012 13:23:18 GMT

Hi,

we can use set analysis,use below exp then calculate

count({$<Clients=-{0},[This month Target]={0}>}User)

Hope this helps..

Sivaraj S

Want to count based on a sum expression field?

Mon, 26 Mar 2012 13:44:03 GMT

Thanks Sivaraj, but I'm not following you here? Maybe showing my current expressions will help in how I could build them into your set analysis solution? I'm sure all the data is linking correctly in the background to do this, it's just getting the script to do it!

The "this month Target" target calculation is:

=Sum(if(JT_TargetMonth = Month(Now(2)) and JT_TargetYear = Year(Now(2)),JT_Target))

The correct clients to count the targets against is:

=Count(DISTINCT if(IsNull(JT_UniqueClientID)=0, JT_UserName)

Thanks!

Nick

Want to count based on a sum expression field?

SunilChauhan — Mon, 26 Mar 2012 13:47:36 GMT

you need to use aggr function

count(if(aggr(sum([This month Target]),User)=0,User))

hope thi helps

Want to count based on a sum expression field?

Mon, 26 Mar 2012 14:08:09 GMT

Thanks Sunil, that nearly worked for me and got me to a solution that has!

Re: Want to count based on a sum expression field?

sivarajs — Mon, 26 Mar 2012 14:13:07 GMT

hi,

can u check this too...see the attached file

Sivaraj S