3 Replies Latest reply: Jun 2, 2017 9:23 AM by Sunny Talwar RSS

    Set Analysis with aggregated function?

    Simon Hogg

      I have a table of customers who have bought things, and some of whom have returned things for a refund.  I want to count the number of customers who have a net positive transaction value;

       

      Row Number        Customer      Transaction Value

          1                    CustomerA    $1000

          2                    CustomerA    -$1000

          3                    Customer B    $500

       

      Should give the answer "One Customer"

       

      Count(Distinct {<[Transaction Value] = {">0"}>} [Customer] ) will give the answer "2" because it will count Row 1 & Row 3.

       

      I have tried Count(Distinct {<Aggr (Sum ([Transaction Value])) = {">0"}>} [Customer] ) but that gives an error in the formula editor.

       

      I also tried, Count(Distinct {<'Sum ([Transaction Value]) >0'>} [Customer] ) which is accepted, but gives the wrong answer (I think it just counts all records

       

      Any clues???

       

      Thanks.