How to calculate average of specific fields?

[Ashwini008]

Hi,

I am formatting data as required and getting it in below format. Now I want to calculate average of only highlighted fields in green color i.e. Q1_score PREPAID,Q2_score PREPAID,Q1_score CONSUMER so on

Example Q1_score CONSUMER ,Count by segment value should be 4.50

This is last piece of my query

| addcoltotals COUNT* Q1* Q2* Q3* Total 
| eval Month=coalesce(Month, "Count by Segment") 

 Please suggest

[somesoni2]

If the field names are static, you can use eventstats to calculate average of those specific fields like this

Your current search generating those columns
| eventstats avg("Q1_score PREPAID") as "Avg Q1_score PREPAID" ,avg("Q2_score PREPAID") as "Avg Q2_score PREPAID",avg("Q1_score CONSUMER") as "Avg Q1_score CONSUMER"

[Ashwini008]

@somesoni2  This isn't giving me any output,I don't see any additional field being added to the result.

Also, I want to show the average for Q1_score PREPAID & so on in  "count by segment" row i.e. at the bottom row

| eventstats avg("Q1_score PREPAID") as "Avg Q1_score PREPAID" ,avg("Q2_score PREPAID") as "Avg Q2_score PREPAID",avg("Q1_score CONSUMER") as "Avg Q1_score CONSUMER"

[Ashwini008 · Accepted solution]

@somesoni2 @PickleRick  Below code was my fix 

| eval var=1 
| addcoltotals COUNT* Q1* Q2* Q3* var Total 
| foreach Q1_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 
| foreach Q2_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 

[PickleRick]

To be fully honest, I don't understand what you want to do. Especially that I don't see any average that should come down to 4.50

But as a general rule, sometimes it's better to do some additional calculations earlier and then aggregate the results in the end instead of - for example - adding totals and then trying to do additional stuff since totals can get in the way.