topic Re: Sum function issues: How to convert monetary number to a number? in Splunk Search

Sum function issues: How to convert monetary number to a number?

jelmalem — Fri, 25 May 2018 16:22:33 GMT

Hi everyone,

I'm beginner on Splunk

I imported my data from a csv file, all the field is correct, I have 4 columns :

ID (String => 54BS26D)
Product Title (String => ABC PRODUCT)
Ordered Revenue (String => $20,530.42)
Ordered Units(Number => 256)

I had two issues :

1) I would like to do a sum of the revenue and i don't know how to do it because for Splunk the field "Revenue" is a string.
2) When i do the sum to find how many pieces i sold with [sourcetype="csv" | stats sum("Ordered Units")], i don't find the same result on excel, i don't know why.

Could you help please.
Thanks

Re: Sum function issues: How to convert monetary number to a number?

somesoni2 — Fri, 25 May 2018 16:44:57 GMT

Try something like this

your current search with fields ID, "Product Title" "Ordered Revenue" and "Ordered Units"
| eval  "Ordered Revenue"=tonumber(replace('Ordered Revenue',"\$|,",""))
| stats sum("Ordered Revenue") as TotalRevenue sum("Ordered Units") as TotalUnitsOrdered by ID "Product Title"

Re: Sum function issues: How to convert monetary number to a number?

jelmalem — Fri, 25 May 2018 17:19:16 GMT

Hi,

Thanks for your quick answer ! It works but :

In order to get just the Total Ordered Revenue in my dashboard I did [eval "Ordered Units"=tonumber(replace('Ordered Units',"\$|,","")) | stats sum("Ordered Units")] i get the good number but without the decimal and the $, how I can do it ?

Thanks

Re: Sum function issues: How to convert monetary number to a number?

somesoni2 — Fri, 25 May 2018 18:01:12 GMT

You can format the results after the stats. So add this after that stats command.

..above search with stats
| eval TotalRevenue="$".tostring(TotalRevenue,"comma")

Re: Sum function issues: How to convert monetary number to a number?

jelmalem — Fri, 25 May 2018 18:58:36 GMT

Thanks again, there was a mistake it's eval TotalRevenue="$".tostring(TotalRevenue,"commas").

Re: Sum function issues: How to convert monetary number to a number?

niketn — Fri, 25 May 2018 19:42:04 GMT

So couple of things about displaying formatted $ amount in table.

While retaining the dollar amount as number, if you need to show $ and command separator you have following two options:

1) Use fieldformat instead of eval

| fieldformat TotalRevenue="$".tostring(TotalRevenue,"commas")

2) If you are on Splunk 6.5 or higher, you can use Table Formatting option from UI Edit to add commas and Unit before or After the Dollar amount as number.

    <format type="number" field="TotalRevenue">
      <option name="unit">$</option>
      <option name="unitPosition">before</option>
    </format>

Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsFormatting#Number_format