Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

quriouss
Contributor 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
MVP

Re: Distinct count based on *results* of calculations

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
MVP

Re: Distinct count based on *results* of calculations

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
Contributor III

Re: Distinct count based on *results* of calculations

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
Contributor III

Re: Distinct count based on *results* of calculations

Thanks - I was writing my reply as you posted!

MVP
MVP

Re: Distinct count based on *results* of calculations

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

Best,

Sunny