4 Replies Latest reply: Aug 19, 2015 12:04 PM by Sunny Talwar RSS

    Distinct count based on *results* of calculations

    Simon Hogg

      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.