Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rossdoug
New Contributor II

counting aggregates question

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: counting aggregates question

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)

, ' ')

6 Replies
brunobertels
Valued Contributor

Re: counting aggregates question

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
New Contributor II

Re: counting aggregates question

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.

MVP
MVP

Re: counting aggregates question

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
New Contributor II

Re: counting aggregates question

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!

MVP
MVP

Re: counting aggregates question

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

brunobertels
Valued Contributor

Re: counting aggregates question

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