I have transactions logged across different sales "channels" (catering, mobileApp, faceToFace, etc.). I am trying to display the transaction Decision
counts along with the percentOfTotal
(Rate) of each Decision
type.
first search:
host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns"
<< this base search is a discrete total of all transactions in all use cases in the catering channel.
second search:
host=server* "Catering" txnType=order issuerResponse="authorized" NOT ("avsMismatch" OR "cvvMismatch") | timechart span=1d dc(txnId) by Decision | rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error"
<< this base search is one use case in the catering channel that is a subset of the first search.
calculation:
| eval percentOfTotal=(round((Decision/totalTxns)*100)."%")
<< I'm intending for this to be percentOfTotal
displayed in a chart for each of the three Decision
types (Accept, Decline, Error) found in the second search over a span=1d. However, in attempting to chart this doesn't seem like a way to accomplish what I am trying to do.
Ideally the data could be presented similar to the following:
-totalTxns
being greater than the sum of all Decisions
is expected as this is only one specific use case within this channel.
Try this; it may not be the most efficient search, but it should work
host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns"
| append [ search host=server* "Catering" txnType=order issuerResponse="authorized"
NOT ("avsMismatch" OR "cvvMismatch")
| timechart span=1d dc(txnId) by Decision
| rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error" ]
| stats first(*) as * by _time
| foreach Accept Decline Error [ eval <<FIELD>>_percentOfTotal=(round((<<FIELD>>/totalTxns)*100)."%") ]
| rename _time as Date, Accept_percentOfTotal as "Accept %", Decline_percentOfTotal as "Decline %",
Error_percentOfTotal as "Error %", totalTxns as "Total Txn"
| table Date "Total Txn" Accept "Accept %" Decline "Decline %" Error "Error %"
Try this; it may not be the most efficient search, but it should work
host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns"
| append [ search host=server* "Catering" txnType=order issuerResponse="authorized"
NOT ("avsMismatch" OR "cvvMismatch")
| timechart span=1d dc(txnId) by Decision
| rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error" ]
| stats first(*) as * by _time
| foreach Accept Decline Error [ eval <<FIELD>>_percentOfTotal=(round((<<FIELD>>/totalTxns)*100)."%") ]
| rename _time as Date, Accept_percentOfTotal as "Accept %", Decline_percentOfTotal as "Decline %",
Error_percentOfTotal as "Error %", totalTxns as "Total Txn"
| table Date "Total Txn" Accept "Accept %" Decline "Decline %" Error "Error %"
Thank you Iguinn! absolutely brilliant!