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
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)
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)
its working! thanks somesoni2
try this,
your base query | table "10-20k", "20-50k","50-100k","100-500k","500k"
"10-20k", "20-50k","50-100k","100-500k","500k" are not colums, they are column values
Post the sample table. Did you try with the query. Because your are group by range.
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