New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator III

Distinct count based on *results* of calculations

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.

1 Solution

Accepted Solutions
MVP

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))

4 Replies
MVP

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))

Creator III
Author

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 )

Creator III
Author

Thanks - I was writing my reply as you posted!

MVP

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

Best,

Sunny

Tags
Community Browser