Splunk Search

How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

ihaveasplunkacc
Loves-to-Learn Lots

The column to the right has a total of the percentage increase, but I would like to take that total and divide it by the number of rows that made the total.

source="im_positions*.csv" sourcetype="stock-Positions:csv"
| dedup Symbol
| rename "Market Value" as mv
| rename "Estimated Gain_Loss" as egl
| rex field=mv mode=sed "s/,//"
| rex field=egl mode=sed "s/,//"
| rex field=Price mode=sed "s/,//"
| replace $* with * in Price
| replace $* with * in egl
| replace N/A with 0 in egl
| replace $* with * in mv
| replace .000 with * in Quantity
| eval originalprice = (mv - egl)/Quantity
| eval eglneg=egl
| replace -
with * in eglneg
| replace $* with * in eglneg
| replace N/A with 0 in eglneg
| eval originalpriceneg = (mv)+(eglneg)

| eval originalprice=if(isnull(originalprice), originalpriceneg, originalprice)

| eval Percent = round(((Price - originalprice)/originalprice*100),0)

| table Symbol Description Quantity originalprice Price egl  mv  Percent
| rename originalprice as "Purchased Price"
| rename Price as "Current Price"
| rename mv as "Market Value"
| rename Percent as "Percentage Increase"
| rename  egl as "Estimated Gain_Loss"
| rename Quantity as "# of Shares"
| addcoltotals 
| sort -"Estimated Gain_Loss"

alt text

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

If you want to add additional columns with statistics, use eventstats:

... | eventstats count sum(foo) avg(foo)

If you just want the statistics, use stats:

... | stats count sum(foo) avg(foo)

I'm not sure what particular statistic you're asking for, so those two are just examples similar to the words you used.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

If you want to add additional columns with statistics, use eventstats:

... | eventstats count sum(foo) avg(foo)

If you just want the statistics, use stats:

... | stats count sum(foo) avg(foo)

I'm not sure what particular statistic you're asking for, so those two are just examples similar to the words you used.

0 Karma

ihaveasplunkacc
Loves-to-Learn Lots

Thank you, but that is not exactly what I am looking for.

Field=percentage with a value=.25
and the second value in the field
Field=percentage with a value=.50

If I add them I get a total of .75, but I want to divide that total by the number of values added to make it. In this case it would be .75/2=.375

So if I I have a column of percentages, I want to add them all up and then divide the total by the number of values that made the sum.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

So... an average?

0 Karma

ihaveasplunkacc
Loves-to-Learn Lots

My bad. You were absolutely correct.
Thank you

0 Karma