Distinct count based on *results* of calculations

[quriouss]

It's late and I'm tired, but I just can't get this to work.

I simply want to get a count of clients for whom their net total spend (A+B+C) is greater than zero, but some invoices are reversed so in the data there is a +100 and -100 (net result zero).

Client          A     B     C

Smith          0    200    0

Jones        100    0      0

Jones       -100    0      0

I *do not* want to include Jones in my count because her net total is 0.

If I do:

     Count (Distinct ( if ((A+B+C) >0 , Client))) then it counts the positive invoice and includes Jones.

How do I get Qlik Sense to act only on the aggregated results?  I think I have given myself a clue there - it's probably something to do with AGGR, but I can't get it to work correctly.

[sunny_talwar · Accepted solution]

May be need to Aggregate by client:

Count (Distinct if(Aggr(A+B+C), Client) >0 , Client))

Count (Distinct if(Aggr(Sum(A+B+C), Client) >0 , Client))

[quriouss]

Once again the sheer act of writing out the question has focused my mind ...

Think of the AGGR() function as a temporary table, and then work on those results, so;

AGGR ( Sum ( A+B+C ) , Client ) will give the net total A+B+C for each client and then I can leverage that with;

COUNT ( Distinct ( if ( AGGR ( Sum (A+B+C) , Client ) > 0 , Client )

[quriouss]

Thanks - I was writing my reply as you posted!

[sunny_talwar]

Ya I saw yours below. I am glad you were able to figure it out

Best,

Sunny