Arcade Expression for Dashboard - GroupBy and Calculate Mean

3103
2
01-04-2022 03:54 AM
Labels (1)
by
New Contributor III

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)

Tags (3)
1 Solution

Accepted Solutions
by
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
2 Replies
by
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
by
New Contributor III

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