topic Re: counting aggregates question in App Development

counting aggregates question

rossdoug — Fri, 20 Nov 2015 16:34:53 GMT

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!

Re: counting aggregates question

brunobertels — Fri, 20 Nov 2015 18:47:20 GMT

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

Re: counting aggregates question

rossdoug — Fri, 20 Nov 2015 18:57:29 GMT

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.

Re: counting aggregates question

maxgro — Fri, 20 Nov 2015 19:44:08 GMT

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)

, ' ')

Re: counting aggregates question

rossdoug — Fri, 20 Nov 2015 23:10:23 GMT

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!

Re: counting aggregates question

maxgro — Sat, 21 Nov 2015 07:37:34 GMT

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

Re: counting aggregates question

brunobertels — Sat, 21 Nov 2015 09:26:14 GMT

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