Splunk Search
Highlighted

Table with with a column that contains positive and negative values. I want to sum the total of all values and have the results as a Total, but it ignores the negative values. What am I doing wrong?

New Member

| table Account "Estimated GainLoss"
| addcoltotals labelfield="Account" label="Totals"
| sort -"Estimated Gain
Loss"

alt text

0 Karma
Highlighted

Re: Table with with a column that contains positive and negative values. I want to sum the total of all values and have the results as a Total, but it ignores the negative values. What am I doing wrong?

New Member

I just realized the sample data I posted had slight formatting error, but the problem is still the same. The samples below have the correct values.

Results in Excel with the output that I am looking for

Account Estimated Gain_Loss
Total 3200
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300

Results in Splunk with incorrect total

Account Estimated Gain_Loss
Total 3500
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300

0 Karma
Highlighted

Re: Table with with a column that contains positive and negative values. I want to sum the total of all values and have the results as a Total, but it ignores the negative values. What am I doing wrong?

Ultra Champion

hi, @ihaveasplunkaccount

| makeresults
| eval raw="Account EstimatedGainLoss
ACCOUNT1 1000
ACCOUNT2 2000
ACCOUNT3 500
ACCOUNT4 -300"
| multikv forceheader=1
| table Account Estimated
Gain_Loss
| addcoltotals labelfield=Account

This query shows correct result. but,

| makeresults
| eval raw="Account EstimatedGainLoss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated
Gain_Loss
| addcoltotals labelfield=Account

this query is not work.

your sample has $ mark. Actually, is this right?

| makeresults
| eval raw="Account EstimatedGainLoss
ACCOUNT1 $1000
ACCOUNT2 $2000
ACCOUNT3 $500
ACCOUNT4 -$300"
| multikv forceheader=1
| table Account Estimated
GainLoss
| eval Estimated
GainLoss=replace(EstimatedGain_Loss,"\$","")
| addcoltotals labelfield=Account

this query excludes $ mark and calculates total.
sorry, why I don't use Code Sample is because extra space is added.

View solution in original post

0 Karma
Highlighted

Re: Table with with a column that contains positive and negative values. I want to sum the total of all values and have the results as a Total, but it ignores the negative values. What am I doing wrong?

New Member

Wow, your sample proved thing correct. Thank you. The problem was with the negative numbers in my sample. They actually had the $ before the value in the field. I thought it was showing up because of table formatting that I had done in my dashboard. I stripped the $ sign out and then the math worked. Your answer is correct and really helped. Thank you! Have a great weekend.

0 Karma