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
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
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
it needs the tick mark if it solved the problem 🙂
it works, Thank you!.