1 Reply Latest reply: Nov 21, 2017 9:42 AM by Andrea Gigliotti RSS

    Aggregation Question for net results - "show me clients who bought over $1000"

    Simon Hogg

      I have a business process (and dataset) where something like this can happen;

       

      Day 1 - Customer purchases something for $1000

      Day 2 - Customer returns that thing for a refund of -$1000

       

      In my dataset I have;

       

      • Transaction Date
      • Store ID
      • Customer ID
      • Purchase Price
      • Refund Price

       

      What I would like to do is "Show me all the Customers who bought (net) more than zero" but if i do something like;

       

      Count (Distinct ( IF (Rangesum ([Purchase Price] , [Refund Price]) > 0 , [Customer ID] , Null()))


      Then it counts each individual row and will include any customer who subsequently returned their purchase on another day (it tests each individual row and then summarise).


      The 'obvious' answer is to do an aggregation, but this gives inconsistent (and, to me, illogical results);


      Count ( Distinct ( IF (

          RANGESUM ( AGGR ( RANGESUM (([Purchase Price] , [Refund Price]) , [Customer ID]))  > 0 ,

                [Customer ID] , Null()))


      Where am I going wrong?  (It sometimes looks it might be only testing the first data line in the load and ignoring the subsequent ones for that client - any ideas???)