Dashboard - Series - How to Group Year of Birth into Age Groups

[TheresaStratmann]

I was wondering if someone could help me with the following task. I have a data set where each row is one person's answer to a questionnaire. One of the columns contains the year they were born in. In a Dashboard I want to track how many people within different age categories have filled out the survey, I want to do this using a bar chart where I can then use a value guide to show the target for each age group (same target for each group). So I my assumption is I need to use the data expressions option. In that,  I have managed to take my data and calculate the age, but despite a lot of Googling, I don't know how to now group the ages into age groups (e.g., 18-30, 31-40, 41-50, 51-60, >60) so I can feed this into the chart. Any help would be much appreciated. 

var fs = FeatureSetByPortalItem(
    Portal('https://www.arcgis.com/'),
    '123456', 0,
    ['year_of_birth_please_write_full'],
    false
);

return GroupBy(
    fs,
    'OBJECTID', // unique ID field 
    [
        {
            name: 'Age_years',
            expression: '2025 - year_of_birth_please_write_full',
            statistic: 'SUM'
        }
     ]

);

[Neal_t_k · Accepted solution]

One option would be to calculate that grouping with something like this 

var fs = FeatureSetByPortalItem(
    Portal('https://www.arcgis.com/'),
    '123456', 0,
    ['year_of_birth_please_write_full'],
    false
);
var currentYear = Year(Now());
var features = [];

for (var row in fs) {
    var birthYear = row.year_of_birth_please_write_full;
    var age = currentYear - birthYear;

    var ageGroup = When(
        age >= 18 && age <= 30, "18-30",
        age >= 31 && age <= 40, "31-40",
        age >= 41 && age <= 50, "41-50",
        age >= 51 && age <= 60, "51-60",
        age > 60, ">60",
        "Unknown"
    );

    Push(features, {
        attributes: {
            BirthYear: birthYear,
            Age: age,
            AgeGroup: ageGroup
        }
    });
}
return FeatureSet(Text({
    fields: [
        { name: "BirthYear", type: "esriFieldTypeInteger" },
        { name: "Age", type: "esriFieldTypeInteger" },
        { name: "AgeGroup", type: "esriFieldTypeString" }
    ],
    geometryType: "",
    features: features
}));

Then you can use the groupby settings and count statistic in your bar chart.

[TheresaStratmann]

Thank-you so much Neal! This worked perfectly! I really appreciate you taking the time!

[KenBuja]

You can use the SQL CASE statement in the first GroupBy to bin the ages. This will also calculate the age group for the current year instead of 2025. The second GroupBy will count the number of each of the age groups

var fs = FeatureSetByPortalItem(
  Portal('https://www.arcgis.com/'),
  '123456', 0,
  ['year_of_birth_please_write_full'],
  false
);
var sql =
  `Case 
  When year_of_birth_please_write_full > ${Year(Now()) - 18} Then '<18'
  When year_of_birth_please_write_full > ${Year(Now()) - 30} Then '18-30'
  When year_of_birth_please_write_full > ${Year(Now()) - 40} Then '31-40'
  When year_of_birth_please_write_full > ${Year(Now()) - 50} Then '41-50'
  When year_of_birth_please_write_full > ${Year(Now()) - 60} Then '51-60'
  else '>60'
  end`
;
var grouped = GroupBy(
  fs,
  'OBJECTID', // unique ID field 
  { name: 'Age_years', expression: sql, statistic: 'MAX' }
);
GroupBy(
  grouped,
  "Age_years",
  { name: "Age_Groups", expression: "1", statistic: "COUNT" }
);

[TheresaStratmann]

Thanks Ken, this is also an good option, in my case I just prefer the finer scale resolution of the output from the first solution. I appreciate the help and seeing how to do things multiple ways! The nice thing about coding 🙂