show data as in the order defined in query

[dsiob]

I have a chart shows counts of Policies under different Policy Amount ranges (eg: 10000-50000).

Query:
index|rename Amount as a |eval range = case (a>=10000 and a <= 20000, "10 - 20k" , a>20000 and a <= 50000, "20 - 50k" ,a>50000 and a <= 100000, "50 - 100k" ,a>100000 and a <= 500000, "100 - 500k" ,a>=500000 , ">500k" )| stats count(Policies) by range

It shows y axis data in chart in order as:

10-20k

100-500k

20-50k
50-100k
500k

It is taking ascending order and re ordering.
But I want in the order I defined the ranges, otherwise chart will become irrelevent.

I want it as :
10-20k
20-50k
50-100k
100-500k
500k

[somesoni2 · Accepted solution]

Try like this (appending serial number will ensure properly sorted output after stats, last eval is to remove those prefix sno)

index|rename Amount as a |eval range = case (a>=10000 and a <= 20000, "1. 10 - 20k" , a>20000 and a <= 50000, "2. 20 - 50k" ,a>50000 and a <= 100000, "3. 50 - 100k" ,a>100000 and a <= 500000, "4. 100 - 500k" ,a>=500000 , "5. >500k" )| stats count(Policies) by range
| eval range=substr(range,4)

[dsiob]

its working! thanks somesoni2

[sbbadri]

try this,

your base query | table "10-20k", "20-50k","50-100k","100-500k","500k"

[dsiob]

"10-20k", "20-50k","50-100k","100-500k","500k" are not colums, they are column values

[sbbadri]

Post the sample table. Did you try with the query. Because your are group by range.

[dsiob]

source="Agent_Details_4.csv" host="397AD-1A210036" sourcetype="csv" | rename "FACE AMOUNT" as p | eval range=case(p>=0 AND p<=100000, "0-100 K", p>100000 AND p<=200000, "100-200 K",
  p>20000 AND p<=50000, "200-500 K",p>500000 AND p<=1000000, "500-1 M",p>1000000 ,  ">1M")|stats count as "Policy count" by range