Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Count based on 2 text field values

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.

CityAttended
PONYES
PON

CHI

YES
CHI
PONYES

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

Tags (1)
1 Solution

Accepted Solutions
rahulpawarb
Valued Contributor III

Re: Count based on 2 text field values

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

8 Replies
Not applicable

Re: Count based on 2 text field values

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_in
Valued Contributor

Re: Count based on 2 text field values

Hi Micahel,

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

Regards,

H

rahulpawarb
Valued Contributor III

Re: Count based on 2 text field values

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
Contributor II

Re: Count based on 2 text field values


Hi,

try this

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

OmarBenSalem
Esteemed Contributor

Re: Count based on 2 text field values

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)

Capture.PNG

Not applicable

Re: Count based on 2 text field values

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

Not applicable

Re: Count based on 2 text field values

Thank you Omar for the explanation along with the expression.

Michael

rahulpawarb
Valued Contributor III

Re: Count based on 2 text field values

Cheers,

Rahul