count elements that pass multiple conditions on the same field

[juanpv20]

Hi

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

Input Table

device id	status
1	103
1	1
1	102
1	114
2	102
2	103
2	114
2	117
3	103
3	114
4	100
4	102

Then I want the following KPI's

condition	devices
status 103	3
status 103 and status 1	1
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!

[shraddha_g]

Try:

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

[ecolomer]

May be

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

[tresesco]

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.

[tresesco · Accepted solution]

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

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

[juanpv20]

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]

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

[juanpv20]

thanks for the answer. This didnt work