topic Re: Calculate average of specific fields in Splunk Search

How to calculate average of specific fields?

Ashwini008 — Tue, 21 Mar 2023 16:20:19 GMT

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

Re: Calculate average of specific fields

PickleRick — Mon, 20 Mar 2023 18:23:36 GMT

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.

Re: Calculate average of specific fields

somesoni2 — Mon, 20 Mar 2023 18:57:55 GMT

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"

Re: Calculate average of specific fields

Ashwini008 — Tue, 21 Mar 2023 06:21:34 GMT

@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"

Re: Calculate average of specific fields

Ashwini008 — Tue, 21 Mar 2023 07:37:53 GMT

@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)]