Having the following search result, I need to calculate total for few rows and average for few rows and both results should be displayed in same column as below:
i_count 310 914 168 31 166 263
r_count 931 900 310 25 231 752
avg_score 4.67 4.36 4.59 4.5 4.76 4.44
prev_score 4.77 4.58 4.44 4.0 4.67 4.63
for the above table , i have to calculate sum for row 1 &2, whereas calculate average for row 3 & 4 in a new column.
the expected result is like
Total
1852
3149
4.55
4.51
I tried using addtotals, but its performing addition for all the rows. Is it possible to add only specific rows and calculate average for specific rows?
well you should try,
| transpose header_field=data
| addcoltotals i_count r_count
| eventstats avg(avg_score) as avgscore avg(prev_score) as prevscore
| eval avg_score=if(like(avg_score,"%%"),avg_score,avgscore),prev_score=if(like(prev_score,"%%"),prev_score,prevscore)
| table i_count r_count avg_score prev_score
| transpose
| rename row3 as ...
here data
is the column name of first column.
let me know if this helps!
Hello,
You can try using transpose
and some eval
calculation after.
And when you are done with your math you can transpose
it back.
you will be able to do something like that after the first transpose
:
| eval Total = i_count + r_count
| eval Average = (avg_score + prev_score) / 2