How can group age?

[gajananh999]

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

[Ayn]

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.

[sideview · Accepted solution]

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.

[sideview]

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

[sideview]

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

[gajananh999]

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