Arcade Expression for Dashboard - GroupBy and Calculate Mean

[DoZ]

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:

Date	City	N1	N2	N3
06.12.2021	Munich	5	5	11
06.12.2021	Berlin	3	2	7
06.12.2021	Hamburg	3	3	9
14.12.2021	Munich	7	1	7
14.12.2021	Berlin	6	2	7
14.12.2021	Hamburg	5	11	8
22.12.2021	Munich	1	2	7
22.12.2021	Berlin	1	4	7
22.12.2021	Hamburg	2	2	7

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.2021	14.12.2021	22.12.2021
Munich	7	5	3,33
Berlin	4	5	4,00
Hamburg	5	8	3,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'), 'xxxxxx' , 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 solution]

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.

[DoZ]

Hello Josh,

thank you so much, it worked perfectly. I copy down here my finale version.

Not sure how you can copy&paste the code here and let it look like the one in your answer, but anyway:

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

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

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

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

return fs_filt

The graph looks like this

This is exactly the result I needed!

Thank you ao much,

Dory