Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
And now I got it working with slight improvement. Try:
=count({<[device id]=p( {<status={'103'}>}) * p({ <status={'1'}>})>} distinct [device id])
Try:
count( {<status={'103','1'}>} distinct device_id)
May be
=count( {<status={'103'}> * <status={'1'}>} distinct device_id)
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.
And now I got it working with slight improvement. Try:
=count({<[device id]=p( {<status={'103'}>}) * p({ <status={'1'}>})>} distinct [device id])
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])
hi thanks for the answer. This is an OR and I wanted an AND
thanks for the answer. This didnt work