Splunk Search

Sorting with Currency Symbol Appended to value

Contributor

I have a query like

``````| stats sum(Price) as TotalPrice by SellerId,Category | fieldformat TotalPrice  = "\$"+ tostring((TotalPrice/1000),"commas") | chart first(TotalPrice) as TotalPrice over SellerId by Category
``````

But the '\$' sign is not appended in the results.

Then I tried

``````| stats sum(Price) as TotalPrice by SellerId,Category | eval TotalPrice  = "\$".tostring((TotalPrice/1000),"commas") | chart first(TotalPrice) as TotalPrice over SellerId by Category
``````

Now i get the \$ sign but the columns cannot be sorted according to price as TotalPrice gets converted to string once i use eval.

Is there a way i can append \$ sign and still sort according to numerical value?

Tags (4)
1 Solution
SplunkTrust

regarding the sorting take this run everywhere example:

``````index=_internal earliest=-15m@m
| bucket _time span=1m
| stats sum(kbps) as mySum by series
| eval mySum=tostring((mySum/1000),"commas")
| eval  TotalPrice  = "\$ "+ mySum
| sort -TotalPrice
``````

this will sort the results by descending `TotalPrice` or you are able to `click` on the `TotalPrice` column to re-sort it.

hope this helps ...

cheers, MuS

SplunkTrust

regarding the sorting take this run everywhere example:

``````index=_internal earliest=-15m@m
| bucket _time span=1m
| stats sum(kbps) as mySum by series
| eval mySum=tostring((mySum/1000),"commas")
| eval  TotalPrice  = "\$ "+ mySum
| sort -TotalPrice
``````

this will sort the results by descending `TotalPrice` or you are able to `click` on the `TotalPrice` column to re-sort it.

hope this helps ...

cheers, MuS

SplunkTrust

you're welcome - you can show your support by donating some karma or sending some beers 🙂

Contributor

I guess to achieve what i want i have to put currency symbol at end than start.

SplunkTrust

Okay this works fine:

``````index=_internal
| bucket _time span=1d
| stats sum(kbps) as mySum by series
| chart first(mySum) as TotalPrice by series
| eval TotalPrice=TotalPrice +" \$"
``````

the main problem is the `tostring((TotalPrice/1000),"commas")` this will break the sorting not because it is a string, but because you add the comma as thousands delimiter....

Contributor

yeah clicking the column sorts considering the values as strings so in ascending order values will look like

\$ 346575
\$ 44
\$ 738
\$74

which is not correct

SplunkTrust

coming back to the run everywhere command, append `chart` and still I'm able to either sort and/or click in the UI to sort the \$ value column:

``````index=_internal earliest=-15m@m
| bucket _time span=1m
| stats sum(kbps) as mySum by series
| eval mySum=tostring((mySum/1000),"commas")
| eval  TotalPrice  = "\$ "+ mySum
| chart first(TotalPrice) as myFirst by series
``````
Contributor

Yeah it works if query ends there... but I have a chart after that and as soon as I add a chart command after that i lose the \$ symbol

Contributor

Thanks Mus for Response.
In Dashboard I have I want the user to click on column name and sort ascending or descending order
SO cannot use the 1st query u provided.

On second search query in your answer, I just get a blank column called TotalPrice but no other column has \$ appended to value.

Don’t Miss Global Splunk
User Groups Week!