topic Re: count elements that pass multiple conditions on the same field in App Development

count elements that pass multiple conditions on the same field

juanpv20 — Tue, 08 Nov 2016 21:22:39 GMT

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!

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

shraddha_g — Wed, 09 Nov 2016 05:59:44 GMT

Try:

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

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

ecolomer — Wed, 09 Nov 2016 06:34:39 GMT

May be

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

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

tresB — Wed, 09 Nov 2016 07:24:35 GMT

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.

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

tresB — Wed, 09 Nov 2016 09:01:57 GMT

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

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

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

juanpv20 — Wed, 09 Nov 2016 12:09:03 GMT

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])

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

juanpv20 — Wed, 09 Nov 2016 12:09:25 GMT

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

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

juanpv20 — Wed, 09 Nov 2016 12:11:13 GMT

thanks for the answer. This didnt work