Splunk Search

Sorting with Currency Symbol Appended to value

adityapavan18
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?

0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

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

View solution in original post

MuS
SplunkTrust
SplunkTrust

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

MuS
SplunkTrust
SplunkTrust

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

0 Karma

adityapavan18
Contributor

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

0 Karma

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

0 Karma

adityapavan18
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

0 Karma

MuS
SplunkTrust
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
0 Karma

adityapavan18
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

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...