Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor III

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

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

1 Reply
Honored Contributor II

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

maybe this:

=Sum( Aggr( If( Sum([Purchase Price]+[Refund Price]) > 0, Sum([Purchase Price]+[Refund Price]), 0 ), [Customer ID] ) )

and untick show zero values.