topic How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.? in Splunk Search

How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

bandit — Tue, 27 Aug 2013 19:41:33 GMT

latest funciton works but avg function does not. I believe splunk is treating my Amount field as a text string. Any way to convert a dollar amount to a numeric value?

Thanks,

Rob

Working:
sourcetype="financials-2" | stats latest(Amount) by Account

    Account     latest(Amount)
1   Advertising $232.62
2   Annual Report   $10.00
3   Electric    $367.73
4   Gazebo Maintenance  $660.00
5   General Maintenance $1,200.00
6   Insurance   $1,638.00
7   Lawn Maint - Extra  $9,200.00
8   Lawn Maintenance    $1,331.00
9   Legal   $231.25
10  Mailing Supplies    $54.62

Not working - returns null values for avg(Amount): 
sourcetype="financials-2" | stats avg(Amount) by Account

Re: How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

jonuwz — Tue, 27 Aug 2013 21:14:46 GMT

Strip all the non-numerical characters out 1st. Splunk is treating the values as a string, not a number.

sourcetype="financials-2" 
| rex mode=sed field=Amount "s/[^\d\.-]//g"
| stats avg(Amount) as AvgAmount by Account
| eval AvgAmount=if(AvgAmount < 0, "-$".trim(AvgAmount,"-"),"$".AvgAmount)

The last line just reintroduces the $ symbol.

Re: How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

kristian_kolb — Tue, 27 Aug 2013 21:24:44 GMT

I think you should keep the decimal dot. Otherwise you'll end up with the the wrong figures.

I suggest the slightly simpler;

... | eval Amt = substr(Amount,2) | convert rmcomma(Amt) | stats avg(Amt) by Account

EDIT: my bad. you did keep it. ... and I didn't remove the comma....

EDIT2: updated above, now without commas.

Re: How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

jonuwz — Wed, 28 Aug 2013 11:18:13 GMT

Amt = substr(Amount,2) keeps the $ if the value is negative.

Re: How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

_gkollias — Wed, 06 Aug 2014 15:18:14 GMT

This works great, however in simple xml, Splunk doesn't like the <. I am getting this error message - Encountered the following error while trying to update: In handler 'views': Error parsing XML on line 67: StartTag: invalid element name. Am I missing something?

Re: How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

somesoni2 — Wed, 06 Aug 2014 15:24:56 GMT

Put your search query within <![CDATA[ your search query here ]]> tag. Alternatively, replace '<' with "<".