topic Re: Formatting Money in Histogram in Splunk Search

Formatting Money in Histogram

splunkqy — Thu, 30 May 2019 13:34:07 GMT

We log money for amounts between $0.01 and $1,000,000,000.00. We are trying to format the histogram labels to show commas and remove trailing zeroes though. This is giving us a problem.

Our query is:

index="<index-name>"
| search <filter-condition>
| where cost > 0
| bin cost span=1log10
| eval Amount="$".cost
| chart count as "Number of Transactions" by Amount

This causes a problem with the amount formatting on the x-axis though.

We tried this query too:

index="<index-name>"
| search <filter-condition>
| where cost > 0
| bin cost span=1log10
| eval Amount="$".tostring(cost, "commas")
| chart count as "Number of Transactions" by Amount

but nothing shows up in the visualization.

How can we remove the trailing zeroes in the decimal part and add commas to thousands parts?

Re: Formatting Money in Histogram

niketn — Thu, 30 May 2019 14:59:04 GMT

@splunkqy try the following:

 index="<index-name>"
| search <filter-condition>
| where cost > 0 
| eval Amount=case(
    cost>=0.0 AND cost<1.0 ,"$0.0-$1.0",
    cost>=1 AND cost<10,"$1-$10",
    cost>=10 AND cost<100,"$10-$100",
    cost>=100 AND cost<1000,"$100-$1000",
    cost>=1000 AND cost<10000,"$1000-$10,000",
    cost>=10000 AND cost<100000,"$10,000-$100,000",
    cost>=100000 AND cost<1000000,"$100,000-$10,00,000",
    cost>=1000000 AND cost<10000000,"$10,00,000-$100,00,000",
    cost>=10000000 AND cost<100000000,"$100,00,000-$10,00,00,000",
    cost>=100000000 AND cost<1000000000,"$10,00,00,000-$100,00,00,000"
    ) 
| bin cost span=1log10 
| stats count as "Number of Transactions" by cost Amount 
| fields - cost

Re: Formatting Money in Histogram

splunkqy — Thu, 30 May 2019 22:01:52 GMT

Thank you. This is perfect.