Dear All,
I have data like
age count
23 76
24 154
25 168
26 140
27 132
28 156
29 152
30 167
31 144
32 133
33 78
Can anyone tell me how can group the age like 20-25,26-30,31-35 and add the counts depending on age.
Thanks
Gajana Hiroji
This search syntax:
... | bin age span=5 | stats sum(count) as count by age
will turn this into rows that have an 'age' field whose values are '15-20', '20-25', '25-30', and a 'count' field whose values are the appropriate sums.
You could use rangemap
:
... | rangemap field=age "20-25"=20-25 "26-30"=26-30 "31-35"=31-35 ... default="other age"
This will create the field range
which holds the ranges you define in your rangemap
command. You can then do
... | rangemap ... | stats count by range
EDIT: ...though sideview's suggestion of using bin
is better.
This search syntax:
... | bin age span=5 | stats sum(count) as count by age
will turn this into rows that have an 'age' field whose values are '15-20', '20-25', '25-30', and a 'count' field whose values are the appropriate sums.
On the other hand if you want to group by the Age brackets in the rows, but split by the Product Name in the columns, then you can only do one statistic at a time, but that would look like this:
index="inforce_analytix" | eval Age=2013-Subscriber_Birth_Year | bin Age span=5 | eval Total_Covered_Amount=(coverage_Amount1+coverage_Amount1) | chart sum(Total_Covered_Amount) over Age by Product_Name
If you want to group by Product_Name as well as Age, so each row is one of the unique combinations of Product_Name and age bracket, then do this:
index="inforce_analytix" | eval Age=2013-Subscriber_Birth_Year | bin Age span=5 | eval Total_Covered_Amount=(coverage_Amount1+coverage_Amount1) | stats sum(Total_Covered_Amount),sum(NumberOfCoveredPersons),sum(Policy_Premium) by Age Product_Name
Thank you for your reply its worked for me
but one small doubt
Age Product_Name Coveamt covper totalpre
20-25 Children Plans 107882008 453 12611869
Investment Plans
Protection Plans
Retirement Plans
Savings Plans
index="inforce_analytix"|eval Age=2013-Subscriber_Birth_Year |bin Age span=5 | eval Total Covered Amount=(coverage_Amount1+coverage_Amount1)| stats values(Product_Name),sum(Total Covered Amount),sum(NumberOfCoveredPersons),sum(Policy_Premium) by Age
I want the data like by product_name also coveramt,covper,totalpre should come