age group report from birth year data

[dhavamanis]

I have a raw data and its contains the user birth Year, city and registered date, how to calculate the age group report based on city and (year quarterly / monthly / daily) .

Data dump like :

---

userID    Birth_year    city       country   registered_date

1           1983         New York    US       04/04/2012

2           1977         Los Angelos US       01/02/1998

---

Sample Results :

---

Age    City       Count

2012-Q1 :

7-14  Los Angelos  25

15-21 Los Angelos  12

2012-Q2 :

22-33 Los Angelos  1050

7-14  New York  2050

15-21 New York  301

22-33 New York  101

[linu1988 · Accepted solution]

The formatting is not really possible in splunk. And i am assuming all the fields are already extracted and accessible by field name. Let's try the below

source=...|eval Qtr=strftime(strptime(registered_date,"%m/%d/%Y"),"%m")|eval Qtr=floor(Qtr%3)+1|eval Qtr="Q".Qtr|eval Year=strftime(strptime(registered_date,"%m/%d/%Y"),"%Y")|eval tage=Year-Birth_year|eval Age_Group=case(tage>6 AND tage<15,"7-14",tage>14 AND tage<22,"15-21",tage>21 AND tage<34,"22-33")|eval YQtr=Year."-".Qtr|stats count as "No of People" by Age_Group,City,YQtr

Thanks

[linu1988]

it needs the tick mark if it solved the problem 🙂

[dhavamanis]

it works, Thank you!.