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?
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
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
you're welcome - you can show your support by donating some karma or sending some beers 🙂
🙂 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.
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....
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
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
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
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.