topic 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? in Splunk Search https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477564#M134013 <P>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.</P> Sat, 22 Feb 2020 00:21:51 GMT ihaveasplunkacc 2020-02-22T00:21:51Z 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? https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477561#M134010 <P>| table Account "Estimated Gain_Loss"<BR /> | addcoltotals labelfield="Account" label="Totals" <BR /> | sort -"Estimated Gain_Loss"</P> <P><IMG src="https://community.splunk.com/storage/temp/282697-sample.jpg" alt="alt text" /></P> Wed, 30 Sep 2020 04:19:02 GMT https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477561#M134010 ihaveasplunkacc 2020-09-30T04:19:02Z 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? https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477562#M134011 <P>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.</P> <P>Results in Excel with the output that I am looking for<BR /><BR /> Account Estimated Gain_Loss<BR /> Total 3200<BR /> ACCOUNT1 1000<BR /> ACCOUNT2 2000<BR /> ACCOUNT3 500<BR /> ACCOUNT4 -300</P> <P>Results in Splunk with incorrect total<BR /><BR /> Account Estimated Gain_Loss<BR /> Total 3500<BR /> ACCOUNT1 1000<BR /> ACCOUNT2 2000<BR /> ACCOUNT3 500<BR /> ACCOUNT4 -300</P> Fri, 21 Feb 2020 23:48:44 GMT https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477562#M134011 ihaveasplunkacc 2020-02-21T23:48:44Z 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? https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477563#M134012 <P>hi, <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/45218">@ihaveasplunkacc</a>ount </P> <P>| makeresults <BR /> | eval _raw="Account Estimated_Gain_Loss<BR /> ACCOUNT1 1000<BR /> ACCOUNT2 2000<BR /> ACCOUNT3 500<BR /> ACCOUNT4 -300"<BR /> | multikv forceheader=1<BR /> | table Account Estimated_Gain_Loss<BR /> | addcoltotals labelfield=Account</P> <P>This query shows correct result. but,</P> <P>| makeresults <BR /> | eval _raw="Account Estimated_Gain_Loss<BR /> ACCOUNT1 $1000<BR /> ACCOUNT2 $2000<BR /> ACCOUNT3 $500<BR /> ACCOUNT4 -$300"<BR /> | multikv forceheader=1<BR /> | table Account Estimated_Gain_Loss<BR /> | addcoltotals labelfield=Account</P> <P>this query is not work.</P> <P>your sample has <CODE>$</CODE> mark. Actually, is this right?</P> <P>| makeresults <BR /> | eval _raw="Account Estimated_Gain_Loss<BR /> ACCOUNT1 $1000<BR /> ACCOUNT2 $2000<BR /> ACCOUNT3 $500<BR /> ACCOUNT4 -$300" <BR /> | multikv forceheader=1 <BR /> | table Account Estimated_Gain_Loss<BR /> | eval Estimated_Gain_Loss=replace(Estimated_Gain_Loss,"\$","")<BR /> | addcoltotals labelfield=Account</P> <P>this query excludes <CODE>$</CODE> mark and calculates total.<BR /> sorry, why I don't use <EM>Code Sample</EM> is because extra space is added. </P> Wed, 30 Sep 2020 04:16:24 GMT https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477563#M134012 to4kawa 2020-09-30T04:16:24Z 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? https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477564#M134013 <P>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.</P> Sat, 22 Feb 2020 00:21:51 GMT https://community.splunk.com/t5/Splunk-Search/Table-with-with-a-column-that-contains-positive-and-negative/m-p/477564#M134013 ihaveasplunkacc 2020-02-22T00:21:51Z