7 Replies Latest reply: May 20, 2016 5:41 AM by Aravind Sasidharan RSS

    Multiple conditional counts in Set Analysis

    Aravind Sasidharan

      I am trying to count the instances where there is a buy indication and a sell indication for an item from two different customers. Same customer can have buy & sell indication in which case it doesn’t count.

       

      This is what I could think of:

       

       

      IF(

      (count({$<Direction = {'SELL'}>} distinct customer_id) > 0) // there is a seller

      AND

      (count({$<Direction = {'BUY'}>} distinct customer_id) > 0) //there is a buy indication from a different customer

      AND

      (count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1) // i.e if number of distinct customers with buy OR sell is also greater than one then it is a match

      , 1, 0)

       

       

      Resulting in the following table

       

      Item, BuySellFlag

       

      A       1

      B       0

      C       1

      D       0

       

      This works but I can’t plot this on time series chart because I always get 1 if the condition is satisfied. Also the totals in the table is also always 1

       

      Is the way to do count( {<the condition above>} item_id) ? Would AGGR help in this case?

       

      I am using QlikSense.

       

      Thanks!