Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
, ' ')
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
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.
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)
, ' ')
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!
If you get the answer, please close the threa, thanks
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