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?

[#2]

try this

=Count({<Answer={'Yes'}>} Participants)/Count({<Answer={'Yes','No'}>} Participants)

if you want to test . try Num and Den individually and see what results you get

[#3]

below will give you in total number of Yes answers as percentage of total yes and no

num(count({<Answer={'Yes'}>}Participant)/count({<Answer={'Yes','No'}>}Participant),'###.##%')

[sunny_talwar]

I have added flags to the script to make it easy:

Script:

Table:

LOAD *,

  If(Answer = 'Yes', 1, If(Answer = 'No', 0)) as Flag;

LOAD Participant,

    Question,

    Answer

FROM

[https://community.qlik.com/thread/164234]

(html, codepage is 1252, embedded labels, table is @1);

Numerator: =Sum(If(Aggr(Sum(Flag), Participant) = 2, 1, 0))

Denominator: =Sum(If(Aggr(Count(Flag), Participant) = 2, 1, 0))

Attaching the qvw for reference.

Best,

Sunny

[#5]

This is just an example and there are several questions in real. This particular expression needs to consider only responses for question A and B.

[#6]

The data model is complex and I do not want to disturb it. Is there a way we can settle this in front end using set analysis expresisons?

[#7]

Hi,

This is just an example and there are several questions in real. This particular expression needs to consider only responses for question A and B.

[sunny_talwar]

I am sure there must be a way to do it in the front end (just haven't figured it out yet), but we are not making too many changes here. You are just adding this on top of your one table. I doubt this is going to break anything.

LOAD *,

  If(Answer = 'Yes', 1, If(Answer = 'No', 0)) as Flag;

I will check if I can find an alternative as well.

Best,

Sunny

[#9]

Thanks Sunny. I have made the changes in the load statement. Can you help me form the expresison using Questions A and B alone? As I mentioned earier to another post, this is just an example and there can be multiple questions. I want to filter based on Questions A and B alone.

[maxgro]

If I understand it should be

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

/

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