The following produces a count of 0 for "Other":
|stats count AS Contacts,
count(eval((in_value=1 AND duration!="")
OR (in_value=2 AND duration!="")
OR (out_value=1 AND duration!="")
OR (out_value=2 AND duration!=""))) AS Successful,
count(eval((in_value=1 AND duration="")
OR (in_value=2 AND duration="")
OR (out_value=1 AND duration="")
OR (out_value=2 AND duration=""))) AS Unsuccessful,
count(eval(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2)) AS Other
BY groupCode
|fields groupCode, Contacts, Successful, Unsuccessful, Other
|addcoltotals Contacts, Successful, Unsuccessful, Other
If I change "Other" to the following, the count can and does include some Successfuls:
count(eval((in_value!=1 AND in_value!=2) OR (out_value!=1 AND out_value!=2))) AS Other
If I change all the ANDs to ORs, I get the same count as Successfuls.
This may not be an answer, but I think it may be easier to understand...
yoursearchhere
| eval success = if( duration!="" AND (in_value==1 OR in_value==2 OR out_value==1 OR out_value==2),1,0)
| eval fail = if(duration="" AND (in_value==1 OR in_value==2 OR out_value=1 OR out_value==2),1,0)
| eval other = if(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2,1,0)
| stats count as Contacts, sum(success) as Successful, sum(fail) as Unsuccessful, sum(other) as Other
by groupCode
| fields groupCode, Contacts, Successful, Unsuccessful, Other
| addcoltotals Contacts, Successful, Unsuccessful, Other
As a diagnostic, just do this:
yoursearchhere
| eval success = if( duration!="" AND (in_value==1 OR in_value==2 OR out_value==1 OR out_value==2),1,0)
| eval fail = if(duration="" AND (in_value==1 OR in_value==2 OR out_value=1 OR out_value==2),1,0)
| eval other = if(in_value!=1 AND in_value!=2 AND out_value!=1 AND out_value!=2,1,0)
| table success fail other duration in_value out_value
and you can probably see what's going wrong. Particularly consider what should happen if any of the fields do not exist...
This helped - I needed to account for fields not existing. Thanks!
Help us understand the relationship (if any) between in_value and out_value.
The fact that you get 0 for Other suggests that the statement with all ANDs cannot be logically true, that is, an event cannot simultaneously have those two fields !=1 AND !=2. Are 1 and 2 the only possible values? Are the values of in_value and out_value linked?