Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

juanpv20
New Contributor

count elements that pass multiple conditions on the same field

Hi

I want to count devices that has multiple conditions on the same field as follow:

Input Table

 

device idstatus
1103
11
1102
1114
2102
2103
2114
2117
3103
3114
4100
4102

Then I want the following KPI's

  

conditiondevices
status 1033
status 103 and status 11
status 103 and status 114 and no status 1

1

I tried something like this without any success for the first KPI:

count( {<status={'103'}>} * {<status={'1'}>} distinct device_id)

Thanks a lot!

1 Solution

Accepted Solutions
MVP
MVP

Re: count elements that pass multiple conditions on the same field

And now I got it working with slight improvement. Try:

=count({<[device id]=p( {<status={'103'}>}) * p({ <status={'1'}>})>} distinct [device id])

7 Replies
shraddha_g
Honored Contributor III

Re: count elements that pass multiple conditions on the same field

Try:

count( {<status={'103','1'}>} distinct device_id)

ecolomer
Honored Contributor II

Re: count elements that pass multiple conditions on the same field

May be

=count( {<status={'103'}> * <status={'1'}>} distinct device_id)

MVP
MVP

Re: count elements that pass multiple conditions on the same field

I thought the expression suggested by ecolomer‌ should work. But somehow that is not working in my sample qvw; not sure if missing something. However, I have tried an alternate expression like:

=count({<[device id]={"=Index(concat(Distinct {<status={1,103}>} status, ',',status), '1,103')"}>}DISTINCT [device id])

and that works, PFA

Edit: With the solution below (I proposed), now I know what I missed with ecolomer‌'s solution.

MVP
MVP

Re: count elements that pass multiple conditions on the same field

And now I got it working with slight improvement. Try:

=count({<[device id]=p( {<status={'103'}>}) * p({ <status={'1'}>})>} distinct [device id])

juanpv20
New Contributor

Re: count elements that pass multiple conditions on the same field

Hi thanks for the answer, it works. Now how can I get : status 103 and status 114 and not status 1?

I can only make it like this:

count({<[device id]=p( {<status={'103'}>}) * p({ <status={'114'}>})>} distinct [device id]) -

count({<[device id]=p( {<status={'103'}>}) * p({ <status={'114'}>})*p({ <status={'1'}>})>} distinct [device id])

juanpv20
New Contributor

Re: count elements that pass multiple conditions on the same field

hi thanks for the answer. This is an OR and I wanted an AND

juanpv20
New Contributor

Re: count elements that pass multiple conditions on the same field

thanks for the answer. This didnt work

Community Browser