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:




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


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


      (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.