Discussion board where members can get started with Qlik Sense.
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.
Go to 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))
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 )
Thanks - I was writing my reply as you posted!
Ya I saw yours below. I am glad you were able to figure it out