topic Chart table expression count if distinct?? in QlikView

Chart table expression count if distinct??

Tue, 01 Mar 2011 01:38:09 GMT

Hello, all and many thanks in advance. Say I have data like the following:

Member Condition Treated

Kevin Blood Pressure No

Tara Blood Pressure Yes

Scott Fracture No

So say I create an expression in a chart table that says:

Count (distinct Member&Condition)

This will return 3.

But say I now want distinct member-condition combinations where Treated = No. How do I do?

Again many thanks in advance!!!

Chart table expression count if distinct??

johnw — Tue, 01 Mar 2011 01:41:02 GMT

I believe this?

count({<Treated={'No'}>} distinct Member&Condition)

Chart table expression count if distinct??

Tue, 01 Mar 2011 01:44:41 GMT

Also, how would I do the following. Data would be:

Member Condition Doctor Treated

Kevin Blood Pressure Greg A Lot

Kevin Blood Pressure John A Little

Kevin Blood Pressure George None

How would I get a count of how many member-condition combinations had at least one doctor who treated "A Lot". In this case Kevin Blood Pressure would get a count of 1. Thanks again!!!

Chart table expression count if distinct??

johnw — Tue, 01 Mar 2011 01:54:45 GMT

Maybe this?

count({<Doctor=P({<Treated={'A Lot'}>} Doctor)>} distinct Member&Condition)

To make any sense of the syntax, look up Set Analysis in the help text index. If it doesn't work or you need clarification, let me know.

Hmmm, no, I bet that's wrong, and that I misunderstood the requirement on first reading. That finds all doctors that have treated anyone for anything 'A Lot', then looks for distinct Member & Condition combinations for those doctors. I'm guessing you only want it when that specific Member & Condition & Doctor was treated 'A Lot'. But then you wouldn't need to mention doctor at all:

count({<Treated={'A Lot'}>} distinct Member&Condition)

So I'm guessing that's not it either? Hmmm, I guess I just don't understand your requirement.

Chart table expression count if distinct??

Tue, 01 Mar 2011 02:15:59 GMT

Thanks for this, John! I'll play around with this tomorrow and let you know how it goes and I will also review Set Analysis. Great tip!

Chart table expression count if distinct??

Tue, 01 Mar 2011 14:25:21 GMT

OK. Here is what I did (a slight variation on problem, but a good test I believe):

Here is the expression:

count({$<ProviderID=P({1<ReviewStatus={'GREEN'}>} ProviderID)>} distinct MemberGenKey&ProviderID)

What I want to be able to do is calculate how many MemberGenKey-ProviderID combinations have at least 1 row where ReviewStatus = Green.

For the data below, I got a result of 15. But I should have gotten a 9:

MemberName	ProviderName	ReviewStatus
A	O	GREEN
B	G	RED
C	N	GREEN
D	D	GREEN
D	K	GREEN
D	M	GREEN
E	H	RED
F	I	GREEN
G	B	RED
H	P	RED
I	P	RED
J	F	GREEN
K	P	RED
L	E	GREEN
M	J	GREEN
N	A	RED
O	C	GREEN
P	P
Q	L	GREEN

Chart table expression count if distinct??

Tue, 01 Mar 2011 17:30:52 GMT

Think I figured it out because order of operations. So using this:

=count({$<ReviewStatus={'Green'}>} distinct (MemberName&ProviderName))

And if I understand correctly, the above is selecting distinct member-provider names from list of rows that have reviewstatus = green, which i think works. Thanks again, John!!!!