topic Re: Help with set analysis in QlikView

Help with set analysis

Fri, 15 May 2015 17:45:49 GMT

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?

Re: Help with set analysis

Fri, 15 May 2015 18:02:03 GMT

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

Re: Help with set analysis

Fri, 15 May 2015 18:09:22 GMT

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),'###.##%')

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:12:46 GMT

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

Re: Help with set analysis

Fri, 15 May 2015 18:13:35 GMT

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.

Re: Help with set analysis

Fri, 15 May 2015 18:16:21 GMT

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?

Re: Help with set analysis

Fri, 15 May 2015 18:17:05 GMT

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.

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:19:19 GMT

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

Re: Help with set analysis

Fri, 15 May 2015 18:27:05 GMT

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.

Re: Help with set analysis

maxgro — Fri, 15 May 2015 18:29:26 GMT

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))

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:30:10 GMT

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))

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:31:38 GMT

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

Re: Help with set analysis

Fri, 15 May 2015 18:45:24 GMT

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

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:47:42 GMT

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))

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:48:21 GMT

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))

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 18:49:55 GMT

Attaching the app with new formulas

Best,

Sunny

Re: Help with set analysis

Fri, 15 May 2015 20:08:57 GMT

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

‌

Re: Help with set analysis

sunny_talwar — Fri, 15 May 2015 20:27:48 GMT

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

Re: Help with set analysis

Anonymous — Fri, 15 May 2015 21:18:36 GMT

Hi, Maybe like this?

Re: Help with set analysis

maxgro — Fri, 15 May 2015 21:24:51 GMT

here the .qvw

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

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