Arcade Expression for Dashboard - GroupBy and Calculate Mean

Question

Hello everyone!

I am trying to summarize a table with data, and I am not sure how to proceed.

I have little programming skills 😞

I have to go from this:

DateCityN1N2N306.12.2021Munich551106.12.2021Berlin32706.12.2021Hamburg33914.12.2021Munich71714.12.2021Berlin62714.12.2021Hamburg511822.12.2021Munich12722.12.2021Berlin14722.12.2021Hamburg227

To a table where for every city AND for every day, the arithmetical mean between N1, N2 and N3 values is calculated. For example, for the City of Munich on the 06/12/2021 the mean of N1, N2 and N3 is 7 (=5+5+11).

06.12.202114.12.202122.12.2021Munich753,33Berlin454,00Hamburg583,67

I am really not sure how/when to loop and when to groupby

if anyone could help I would much appreciate it.

Thanks a Lot!

Dory

var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), 'xyz' , 0, ['Date1', 'City', 'N1', 'N2','N3'], false);

var datefilter = Date(2020,12,31)
var fs_filt = Filter(fs, 'Datum_Meldung > @datefilter')

var fs_mean = {
fields: [
{ name: "Date1", type: "esriFieldTypeDate" },
{ name: "City", type: "esriFieldTypeString" },
{ name: "N_mean", type: "esriFieldTypeDouble" },
],
geometryType: "",
features: [],
};

var index = 0
for (var feature in fs_filt) {
fs_mean.features[index] = {
'attributes': {
'City' : ['City'],
'Datum': ['Date1'],
'N_mean': Sum(feature[....????)/3)

jcarlson · Accepted Answer

It really depends on how you need to use the output. Is this for some report, or a dashboard?

The built-in GroupBy function should be able to give you what you need.

var group_exp = {
    name: 'N_Mean',
    expression: '(N1 + N2 + N3) / 3',
    statistic: 'SUM'
}

return GroupBy(
    fs_filt,
    ['Date1', 'City'],
    group_exp
)

One issue with the sample output table you've posted is that there is a new column for each date. While that is possible, it's not as nice to work with, and if you have many dates, can quickly become a rather unwieldy table.

Keeping the output of GroupBy will give you a row for each city/date combination, which can easily be visualized or further modified by other tools.