counting aggregates question

[rossdoug]

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!

[brunobertels]

Hi Doug

Try this

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

Hope this help

To help you with set analysis ses this tool :

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Bruno

[rossdoug]

Thanks for the response.  The only difference I see there is the single quotes around the 1, right?  I tried it and got the same result as I did without the quotes.

[maxgro · Accepted solution]

in a textbox you can get A B

for count replace concat with count

=

concat(

aggr( if(count({$ <TestOrControl={C}>} Household)>0

  and count({$ <Response={1}>} Household)>0

  ,

  Household

  ),

  Household)

, ' ')

[rossdoug]

Thanks for the reply.   I think that will work for me.  I pulled out concat and replaced with count:

count(aggr( if(count({$ <TestOrControl={T}>} Household)>0 and count({$ <Response={1}>} Household)>0, Household ), Household))

That gave me the result of 2 that I was looking for.  I need to put this into my real application and into some calculations involving sales, but I think it will do the trick.  Thanks for the help!

[maxgro]

If you get the answer, please close the threa, thanks

[brunobertels]

Hi

Yes the only différence was the single quotes.

I'am not sure to understand your need  :

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

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

you have three distinct household

three testorcontrole = T

and only one houshold with a T as testorcontrol and 1 for response gives 1 occurence and not 2 ?

See the qvf attached where i test the different count method

Are you sure is that what you want ?

bruno