How to count cumulative average by month?

[kmfpo]

Hello all.  I am trying to find the average by closed_month, but I want the average duration to include events from previous months in its average.

So, average for Feb should include Jan + Feb.  Average for March should include Jan + Feb + Mar.  I figured out how to get the average for each month, but I don't know how to get include the previous months average_duration along with the current month.

Sample Table of Data

Case    opened  closed  closed_month    duration
aaa     Jan-01  Jan-31  Jan             30
bbb     Feb-10  Feb-26  Feb             16
ccc     Feb-13  Feb-28  Feb             15
ddd     Feb-14  Feb-28  Feb             14
eee     Feb-17  Mar-01  Mar             11
fff     Feb-24  Mar-13  Mar             17
ggg     Mar-03  Mar-11  Mar             8
hhh     Mar-11  Mar-16  Mar             5
iii     Mar-22  Mar-24  Mar             2

Avg Jan = (30) = 30
Avg Feb = (30+16+15+14)/4 = 18.8
Avg Mar = (30+16+15+14+11+17+8+5+2)/9 = 13.1 

The desired result is a column chart, with 3 columns, one for each resolved month.  Then each would go have the value 30, 18.8, and 13.1 respectively.

[scelikok]

Hi @kmfpo,

Since the closed_month is not time, time conversion functions are not working. You can use below;

| makeresults 
| eval _raw="Case    opened  closed  closed_month    duration
aaa     Jan-01  Jan-31  Jan             30
bbb     Feb-10  Feb-26  Feb             16
ccc     Feb-13  Feb-28  Feb             15
ddd     Feb-14  Feb-28  Feb             14
eee     Feb-17  Mar-01  Mar             11
fff     Feb-24  Mar-13  Mar             17
ggg     Mar-03  Mar-11  Mar             8
hhh     Mar-11  Mar-16  Mar             5
iii     Mar-22  Mar-24  Mar             2" 
| multikv forceheader=1 
| fields - _raw _time 


| streamstats sum(duration) as total count as cases 
| stats max(cases) as cases max(total) as total by closed_month 
| eval average=total/cases 
| eval month=case(
    closed_month="Jan","01",
    closed_month="Feb","02",
    closed_month="Mar","03",
    closed_month="Apr","04",
    closed_month="May","05",
    closed_month="Jun","06",
    closed_month="Jul","07",
    closed_month="Aug","08",
    closed_month="Sep","09",
    closed_month="Oct","10",
    closed_month="Nov","11",
    closed_month="Dec","12") 
| sort month
| fields - month

[manjunathmeti]

hi @kmfpo,

You can also use accum command to calculate the running total of duration values.

| stats count, sum(duration) as duration, last(closed) as closed by closed_month 
| eval closed=strptime(closed, "%b-%d") 
| sort closed 
| accum duration as total_duration 
| accum count as total_count 
| eval Avg=round(total_duration/total_count, 1), temp=1, closed_month="Avg ".closed_month 
| xyseries temp, closed_month, Avg 
| fields - temp

If this reply helps you, a like would be appreciated.

[ITWhisperer · Accepted solution]

| makeresults | eval _raw="Case    opened  closed  closed_month    duration
aaa     Jan-01  Jan-31  Jan             30
bbb     Feb-10  Feb-26  Feb             16
ccc     Feb-13  Feb-28  Feb             15
ddd     Feb-14  Feb-28  Feb             14
eee     Feb-17  Mar-01  Mar             11
fff     Feb-24  Mar-13  Mar             17
ggg     Mar-03  Mar-11  Mar             8
hhh     Mar-11  Mar-16  Mar             5
iii     Mar-22  Mar-24  Mar             2"
| multikv forceheader=1
| fields - _raw _time


| streamstats sum(duration) as total count as cases
| stats max(cases) as cases max(total) as total by closed_month
| eval average=total/cases

[kmfpo]

@ITWhisperer   The only thing wrong that needs adjustment is the month sorting.  It is listed as Feb, Jan, Mar (alphabetical I am guessing).  How can I force the order to be calendar order (Jan, Feb, Mar...)?

[ITWhisperer]

You should parse the date strings to epoch time values

| eval time=strptime(time, "%Y-%m-%d %H:%M:%S")

or what time format you are using in your real events

[gcusello]

Hi @kmfpo,

in other words, you want data from the 1st of january of the current year to the last day of the previous month, is this correct?

If this is your need, you habe to add to your main search these time qualifiers:

earliest=@y latest=@mon

Ciao.

Giuseppe