How to find the sum of FieldA for each distinct FieldB and timechart the average of each unique sum?

[mibaker_arrow_c]

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

[woodcock]

Try this:

... | stats latest(_time) AS _time, sum(Inv) AS InvoiceTotal BY "Invc Sales Order Number" | timechart avg(InvoiceTotal)

[jacobwilkins]

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)

[aweitzman]

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?