Invc Sales Order Number = Invoice # that will exist across multiple events
Inv = $$
I need to sum the Inv for Each Distinct "Invc Sales Order Number", then average the total to arrive at an Average for each unique sum. Then i would like to timechart it to show trend.
With the search below I am able to calculate and display an accurate Average, but am not able to timechart the Average below:
......| stats dc("Invc Sales Order Num") as Invoices sum(Inv) as Revenue | eval Average=Revenue/Invoices
Try this:
... | stats latest(_time) AS _time, sum(Inv) AS InvoiceTotal BY "Invc Sales Order Number" | timechart avg(InvoiceTotal)
Does each distinct invoice number only have one value of _time associated with it? Lets assume that you are OK with using the value of _time from the last event for a particular invoice. Lets also use rename
on that nasty field name.
| rename "Invc Sales Order Num" AS InvoiceNum
| stats latest(_time) AS _time, sum(Inv) AS totalInv by InvoiceNum
| timechart avg(totalInv)
Your stats
line doesn't appear to give you what you described; it appears to give you an overall average rather than an average per invoice number. It seems like you would want something like this instead:
| timechart avg(Inv) as AvgRevenue by "Invc Sales Order Num"
Or am I misunderstanding this?