topic Re: Sorting with Currency Symbol Appended to value in Splunk Search

Sorting with Currency Symbol Appended to value

adityapavan18 — Wed, 21 May 2014 08:38:42 GMT

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?

Re: Sorting with Currency Symbol Appended to value

MuS — Wed, 21 May 2014 09:07:34 GMT

Hi adityapavan18,

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

Re: Sorting with Currency Symbol Appended to value

adityapavan18 — Wed, 21 May 2014 09:23:41 GMT

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.

Re: Sorting with Currency Symbol Appended to value

adityapavan18 — Wed, 21 May 2014 13:09:33 GMT

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

Re: Sorting with Currency Symbol Appended to value

MuS — Wed, 21 May 2014 13:16:23 GMT

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

Re: Sorting with Currency Symbol Appended to value

adityapavan18 — Thu, 22 May 2014 12:11:46 GMT

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

Re: Sorting with Currency Symbol Appended to value

MuS — Thu, 22 May 2014 12:54:02 GMT

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....

Re: Sorting with Currency Symbol Appended to value

adityapavan18 — Thu, 22 May 2014 13:20:12 GMT

🙂 This is a long comments section, very much appreciate all the help you are providing.

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

Re: Sorting with Currency Symbol Appended to value

MuS — Thu, 22 May 2014 13:23:34 GMT

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