topic Re: Counting Entries spread on multiple rows with multiple criteria in QlikView

Counting Entries spread on multiple rows with multiple criteria

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

My data consists of something similar

mytable:

LOAD * INLINE [GroupId, Question, Answer

G1, Q1, 3

G1, Q2, B

G2, Q1, 5

G2, Q2, B

G3, Q1, 1

G3, Q2, C

G4, Q1, 1

G4, Q2, B

G5, Q1, 3

G5, Q2, D

G6, Q1, 2

G6, Q2, B

];

Each GroupID(G1..G6) has 2 questions in it (Q1 and Q2)

Q1: Possible Answers: 1,2,3,4,5

Q2: Possible Answers: A,B,C,D

What I want to do is count Unique GroupIDs where Q1 was answered less than or equal to 4 and Q2 was answered B

In the Above data the count should be 3

thank you all

Re: Counting Entries spread on multiple rows with multiple criteria

sunny_talwar — Thu, 17 Aug 2017 16:41:21 GMT

May be this

Count(DISTINCT {<GroupId = {"=Only({<Question = {Q1} Answer) <= 4 and Only({<Question = {Q2} Answer) = B"}>} GroupId)

Re: Counting Entries spread on multiple rows with multiple criteria

sunny_talwar — Thu, 17 Aug 2017 16:43:25 GMT

My bad, I had some typos, try this

Count(DISTINCT {<GroupId = {"=Only({<Question = {Q1}>} Answer) <= 4 and Only({<Question = {Q2}>} Answer) = 'B'"}>} GroupId)

Re: Counting Entries spread on multiple rows with multiple criteria

antoniotiman — Thu, 17 Aug 2017 16:54:06 GMT

Another way

=Count({<GroupId=P({<Question={'Q1'}, Answer={'<4'}>})*P({<Question={'Q2'}, Answer={'B'}>})>} DISTINCT GroupId)

Re: Counting Entries spread on multiple rows with multiple criteria

Anonymous — Mon, 21 Aug 2017 11:26:00 GMT

Thank you both

Since in my original data the Q1 answers are string rather than numeric, I used the following:

=Count({<GroupId=P({<Question={'Q1'}, Answer-={'5'}>})*P({<Question={'Q2'}, Answer={'B'}>})>} DISTINCT GroupId)

thanks

Re: Counting Entries spread on multiple rows with multiple criteria

antoniotiman — Mon, 21 Aug 2017 15:35:55 GMT

Yes, or

Answer={'1','2','3','4'}

if You need.

Thanks and Regards,

Antonio