Splunk Search

How can group age?

gajananh999
Contributor

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

Tags (1)
0 Karma
1 Solution

sideview
SplunkTrust
SplunkTrust

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.

View solution in original post

Ayn
Legend

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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

0 Karma

gajananh999
Contributor

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

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...