I'm not able to solve a strange issue...
Basically, the stats avg() is omitting values in the calculation.
Code is the following:
<<search>> | fillnull Incident_Duration,Unavailabilty_Month value=0 | stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
So, null value are transformed into 0 in the table resulting from the previous search, but avg function is not considering them while processing Incident_Avg and Unavailabilty_Avg
Seems to me very strange... how can i solve?
Tks in advance!
can you try:
..|eval Incident_Duration=tonumber(Incident_Duration), Unavailabilty_Month=tonumber(Unavailabilty_Month) | fillnull Incident_Duration,Unavailabilty_Month value=0 | stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
i think it might be that your values are strings and not numbers. using this runanywhere command:
|makeresults|eval data=" ,2,1,4,6"|makemv delim="," data|mvexpand data|eval with_zero=tonumber(data)|fillnull with_zero value=0|stats avg(data) avg(with_zero), the values are different.
I found the root problem... basically RegMonthAva had some <> value (corresponding with <> values for Unavailability_month and Incident_Duration).
With | fillnull i solve the problem.
I am glad that my solution helped you to get the desired results. I am converting my comment to an answer. Accept/upvote it if this is an answer to your question!