cid=1 Status = 'Delivery failed' delivery_date = 2015-01-01
cid=1 Status = 'Delivery delayed' delivery_date = 2015-02-01
cid=1 Status = 'Delivery failed' delivery_date = 2015-03-01
cid=1 Status = 'Delivery failed' delivery_date = 2015-04-01
cid=2 Status = 'Delivery failed' delivery_date = 2015-01-01
cid=2 Status = 'Delivery failed' delivery_date = 2015-04-01
For the above data I would like to get the TotalDliveryFailedcount as only '2' (1 for cid=1 (even though it has 3 'Delivery failed' events) and 1 for cid=2 (event though it has 2 'Delivery failed' events)
cid=1 Status = 'Delivery failed' phonestatus="Invalid" delivery_date = 2015-01-01
cid=2 Status = 'Delivery failed' phonestatus="Valid" delivery_date = 2015-01-01
cid=3 Status = 'Delivery failed' phonestatus="InValid" delivery_date = 2015-01-01
I would like the output to be Total (DelvieryFailed and Invalid) =2 (i.e cid=1 and cid=3)
Any help is appreciated. Thank you.
You can use disctinct_count() or in short form dc() statistical function.
<your base search> Status="'Delivery failed'" phonestatus="Invalid"
| stats dc(cid) as TotalDliveryFailedcount
PS: I have added phonestatus="Invalid" as per your second question. For your first question you can remove the filter.
In your question you have mentioned phonestatus as invalid and in your example as Invalie and InValid... Since, field values are not case sensitive above search should be able to find all scenarios.
You can use disctinct_count() or in short form dc() statistical function.
<your base search> Status="'Delivery failed'" phonestatus="Invalid"
| stats dc(cid) as TotalDliveryFailedcount
PS: I have added phonestatus="Invalid" as per your second question. For your first question you can remove the filter.
In your question you have mentioned phonestatus as invalid and in your example as Invalie and InValid... Since, field values are not case sensitive above search should be able to find all scenarios.
Thanks a lot for the answer.
Also, based on the data in my 1st question, How can I get the count of Cust_Id's which has Status = 'Delivery failed' and Status = 'Delivery delayed' ? Is there a way to generate a graph showing the actual count to cust_id's and cust_id's with Status = 'Delivery failed' and Status = 'Delivery delayed' (i.e to show what percent of cust_id's have Status = 'Delivery failed' and Status = 'Delivery delayed').
Thanks !
@repo12.... please take out Status filter from Base Search add Status as the by clause in your query
<your base search> Status=* phonestatus="Invalid"
| stats dc(cid) as TotalDliveryFailedcount by Status
Thank you ! That worked