I am trying to calculate the average for a few columns and rows but I have came across the following issue. Some rows might not have data for a particular column, but it seems these are still taken into account when calculating the avg for this column which means the data is way off. I'd like my formula to ignore a data point (if not present) when calculating the average for the column.
Here is my formula :
table ABC* | stats avg(ABC_*)
So if I have something like :
Column : ABC_1 | ABC_2 | ABC_3 | ABC_4 | ABC_5 1 2 1 2 2 2 2 1 1 2 3
The average for column ABC_2, ABC_4 and ABC_5 is incorrect.
I'm not sure that the problem is the avg function. Because the avg function returns the average of the values of field X. Means the null value of the field X is not taking into account by avg(X). In another words, in your table, avg(ABC_2) must return 2.
Check the problem elsewhere
I like this approach but I can't get it to work. I get the following error:
Error in 'eval' command: The expression is malformed. An unexpected character is reached at '/count_*'.
Here is how my search currently looks
Some initial event filtering | table time_svc_agg* | stats count(time_svc_agg_) AS count_, sum(time_svc_agg_) AS sum_ | eval avg_=sum_/count_*
It looks like you will have to iterate each variable as you need it in the "eval" part like this:
| table time_svc_agg* | stats count(time_svc_agg_*) AS count_*, sum(time_svc_agg_*) AS sum_* | eval avg_1=sum_1/count_1 | eval avg_2=sum_2/count_2 ...