topic Re: How to sum correctly? in Splunk Search

How to sum correctly?

tonahoyos — Tue, 29 Sep 2020 16:24:01 GMT

Hello All,

I am having an issue using the stats sum command. This is currently my search:

source="Jan_Sept_FinanceSample.csv" host="Jan_September" index="finance_sample" sourcetype="csv" ObjectAccount="411010" OR ObjectAccount="5*"
| eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount,"5%"),DomesticAmount,0)
| stats sum(Sales) as Sales , sum(Costs) as Costs

I checked if the values were correctly summed on excel, and it turns out that the sums are off. What can it be? I tried to erase and upload the data again, I tried to reformat the values, etc. Is this the wrong command to use?

There are positive and negative values on the Domestic Amount field, I am not sure if it is taking all of the negative values.

Best

Re: How to sum correctly?

elliotproebstel — Mon, 23 Oct 2017 21:06:22 GMT

Have you checked to make sure the values populating the Sales and Costs fields match what you expect? Maybe try this to be sure:

source="Jan_Sept_FinanceSample.csv" host="Jan_September" index="finance_sample" sourcetype="csv" ObjectAccount="411010" OR ObjectAccount="5*" | eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount,"5%"),DomesticAmount,0) | stats list(Sales) as Sales , list(Costs) as Costs BY ObjectAccount

If that's not the issue, are the values you are summing particularly large? Negative values alone should not cause this problem.

Re: How to sum correctly?

tonahoyos — Wed, 25 Oct 2017 20:55:56 GMT

Hello eliotproebstel,

When I do the command values(DomesticAmount), it gives me all the values that need to be added. But once I run the command sum(DomesticAmount), it gives me a completely different answer than what I know should be. On one of the occasions, a warning showed up stating that some values may have been truncated or ignored. The sum is of of 8 digits, but the values are not larger than 5 digits. Would this be an issue?

Re: How to sum correctly?

niketn — Wed, 25 Oct 2017 21:16:54 GMT

@tonahoyos, by testing in Excel, do you mean you ran query in Splunk without the final stats pipe and exported the result as CSV using Export button in Splunk Search UI. And then calculate the sum by opening CSV in Excel?

source="Jan_Sept_FinanceSample.csv" host="Jan_September" index="finance_sample" sourcetype="csv" ObjectAccount="411010" OR ObjectAccount="5*"
| eval Sales=if(ObjectAccount="411010",DomesticAmount,0), Costs=if(like(ObjectAccount,"5%"),DomesticAmount,0)

Re: How to sum correctly?

DalJeanis — Wed, 25 Oct 2017 21:46:53 GMT

First do this and see if the results look reasonable...

source="Jan_Sept_FinanceSample.csv" host="Jan_September" index="finance_sample" sourcetype="csv" 
| stats sum(DomesticAmount) as TotalAmount by ObjectAccount
| appendpipe [ | eval ObjectAccount = substr(ObjectAccount ,1,1)." Total" | stats sum(TotalAmount) as TotalAmount  by ObjectAccount ]

Re: How to sum correctly?

tonahoyos — Fri, 27 Oct 2017 15:25:11 GMT

The results are exactly the same as before. It seems as if some values are being ignored. Thank you for your help!

Re: How to sum correctly?

tonahoyos — Fri, 27 Oct 2017 15:27:30 GMT

I double checked my answers with the original excel dataset that I uploaded in Splunk. It seems as if it was not reading some values.