6 Replies Latest reply: Nov 21, 2015 4:26 AM by bruno bertels RSS

    counting aggregates question

    Doug Ross

      Hello all,

      I am new to the community and Qlik Sense. I’ve looked around for an answer to something I’m trying to do but haven’t found the answer anywhere yet.   I think the solution to my problem is some sort of combination of aggr() and set analysis, but I haven’t come up with a solution yet.  Here’s an example of some data I have:

       

      Individual

      Household

      TestOrControl

      Response

      1

      A

      T

      0

      2

      A

      C

      1

      3

      B

      T

      1

      4

      B

      C

      0

      5

      C

      T

      0

      6

      C

      C

      0

       

      I am trying to do a count of the number of households where anyone in the household has a ‘T’ for TestOrControl and anyone in the household has a ‘1’ for response.  So, in this example, I should get 2 as a result.

       

      The expression that I’m currently using is only coming up with 1 as a result, only counting household B because there is an individual that has both conditions that I’m looking for (individual 3) when I really want to include household A because, as a whole, the two individuals combing to meet my two criteria.

       

      Here’s what I have right now:

       

      count({<TestOrControl={'C'}, Response={1}>} distinct Household)

       

      I know I need to get an aggr() in there somewhere, but I can’t figure out how.  The problem is that I have two conditions that I’m looking at instead of just one.  I can get one of those to work, but not both. 

       

      I’ve also been doing some research on using FirstSortedValue() and that looks somewhat promising as well, but I’m not sure how to incorporate that here.

       

      Any suggestions on where to start on this?

       

      Thanks in advance!