Hi everyone,
I'm beginner on Splunk
I imported my data from a csv file, all the field is correct, I have 4 columns :
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
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"
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"
Thanks again, there was a mistake it's eval TotalRevenue="$".tostring(TotalRevenue,"commas").
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
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
You can format the results after the stats. So add this after that stats command.
..above search with stats
| eval TotalRevenue="$".tostring(TotalRevenue,"comma")