Formatting Money in Histogram

[splunkqy]

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?

[niketn · Accepted solution]

@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

[splunkqy]

Thank you. This is perfect.