Count based on 2 text field values

[#1]

Hello, I am new to Qlik and not the best at writing expressions, I have searched the discussions to find an answer to how I can write expression to count  city based on a "yes" in a separate text field.  I have found and tried many options but cannot seem to get the count.

Below is example.

City	Attended
PON	YES
PON
CHI	YES
CHI
PON	YES

I want to count PON when "Attended"="YES" so CHI should be ignored and null values should also be ignored.

The answer that I would expect in the above example is 2 however I cannot seem to write the expression.

Any help would be appreciated.

Mike

[#2]

Further I would like to report the result as a percentage of all PON residents that have a "YES" for Attended compared to the null value.

Thank you in advance for any help.

Mike

[hector_munoz]

Hi Micahel,

Try: Count({<City={PON}, Attended={YES}>} City) or Sum({<City={PON}, Attended={YES}>} 1).

Regards,

H

[rahulpawarb · Accepted solution]

Hello Micahel,

Further modified Hector's response:

//To Count PON where Attended =YES
Count({<City={'PON'}, Attended={'YES'}>} City)
Sum({<City={'PON'}, Attended={'YES'}>} 1)

//% of all PON residents with YES for Attended compared to the null value

Num(Count({<City={'PON'}, Attended={'YES'}>}City) / (Count({<City={'PON'}>}City)-Count({<City={'PON'}, Attended={'YES'}>}City)), '#,##0%')
//or
Num(Count({<City={'PON'}, Attended={'YES'}>}City) / Count({<City={'PON'}, Attended=-{'YES'}>}City), '#,##0%')

Regards!

Rahul

[viveksingh]

Hi,

try this

Count({<City={ 'PON' }, Attended={ 'YES' }>} City)

[OmarBenSalem]

You use :

Count({<City={'PON'},Attended={'YES'}>}City)

Let's break this expression to explain:

What do we want to do ?

1) Counting the Cities => Count(City)

2) Now, do we want to call all the cities? Or count some of them based on conditions? The answer is count based on conditon; so he write a set expression:

a) 1st step : Count({<>}City)

b) Now we write our conditions between the <> :

1st condition: You want to count only the City PON => City={'PON'} we surrond it by ' ' because it's a string.

2nd condition: You want to count only Attended Yes => Attended={'YES'} we surrond it by ' ' because it's a string.

Result:

Count({<City={'PON'},Attended={'YES'}>}City)

[#7]

Thank You Rahul, This gives me the result that I am looking for while also providing the percentage

[#8]

Thank you Omar for the explanation along with the expression.

Michael

[rahulpawarb]

Cheers,

Rahul