Want to count based on a sum expression field?

[#1]

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

[sivarajs]

Hi,

we can use set analysis,use below exp then calculate

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

Hope this helps..

Sivaraj S

[#3]

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

[SunilChauhan]

you need to use aggr function

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

hope thi helps

[#5]

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

[sivarajs]

hi,

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

Sivaraj S