Help with set analysis

[#1]

Hi all,

I have a data set as shown

Participant	Question	Answer
A	1	Yes
A	2	No
B	1	Yes
B	2	Yes
C	1	N/A
C	2	N/A
D	1	Yes
D	2	N/A

A participant get to answer either yes, no or N/a for Questions 1 and 2.

My requirement is to find the percentage of responses who answered yes to question 1 and question 2 among total responses. N/A should not be considered.

Answer = ( count of responses who answered yes to question 1 and question 2) / ( count of responses to question 1 which was yes)

In above example , 3 participants answered yes to question 1 and there was one yes and one no to question 2 ( don't count N/A)

therefore- answer = 50% or (1/2)

Numerator = count({ <Question={1} , Answer={Yes}, Question={2},Answer={Yes}> }   Participant) <-- not working

Denominator= count({<Question={1},Answer={'Yes'},Question={2} ,match(Answer,'Yes','No') > } Participant)  -< not working

Any help on how I need to form set analysis expression for Numerator and denomitator?

[sunny_talwar]

Try this:

Numerator: =Sum({<Question = {'A', 'B'}>} If(Aggr(Sum({<Question = {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

Denominator: =Sum({<Question = {'A', 'B'}>} If(Aggr(Count({<Question = {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

[sunny_talwar]

Or this for just A and B

sum({<Question = {'A', 'B'}>}aggr(if(concat({<Question = {'A', 'B'}>}Answer, '', Question)='YesYes',1), Participant))

/

sum({<Question = {'A', 'B'}>}aggr(if(match(concat({<Question = {'A', 'B'}>}Answer, '', Question),'YesNo','YesYes'),1), Participant))

I still feel that Flags are better, don't you think so Massimo‌?

Best,

Sunny

[#4]

I tried both the expressions and still not getting the right answer. Can you please send the qlikview file once again if you can? I agree the flags are better as well. I still am unable to get the expresison right

[sunny_talwar]

My bad Questions are 1 and 2. It is the participants that are A and B,

So try this:

Numerator: =Sum({<Participant= {'A', 'B'}>} If(Aggr(Sum({<Participant= {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

Denominator: =Sum({<Participant= {'A', 'B'}>} If(Aggr(Count({<Participant= {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

[sunny_talwar]

Or this from Massimo

sum({<Participant= {'A', 'B'}>}aggr(if(concat({<Participant= {'A', 'B'}>}Answer, '', Question)='YesYes',1), Participant))

/

sum({<Participant= {'A', 'B'}>}aggr(if(match(concat({<Participant= {'A', 'B'}>}Answer, '', Question),'YesNo','YesYes'),1), Participant))

[sunny_talwar]

Attaching the app with new formulas

Best,

Sunny

[#8]

Looks like I made a mistake when I earlier mentioned questions A and B instead of 1 and 2.

We need to consider all participants but just questions 1 and 2.

Thanks for all your effort

Numerator: =Sum({<Question = {'A', 'B'}>} If(Aggr(Sum({<Question = {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

Denominator: =Sum({<Question = {'A', 'B'}>} If(Aggr(Count({<Question = {'A', 'B'}>}Flag), Participant) = 2, 1, 0))

This is what I used. The numerator seems fine but the denominator is having issues

‌

[sunny_talwar]

‌Replace A and B with 1 and 2 and see if that resolves all your issue.

[Anonymous]

Hi, Maybe  like  this?

[maxgro]

here the .qvw

sum(aggr(if(concat(Answer, '', Question)='YesYes',1), Participant))

/

sum(aggr(if(match(concat(Answer, '', Question),'YesNo','YesYes'),1), Participant))