Arcade Expression for Dashboard - GroupBy and Calculate Mean

836
2
Jump to solution
01-04-2022 03:54 AM
Labels (1)
DoZ
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:

DateCityN1N2N3
06.12.2021Munich5511
06.12.2021Berlin327
06.12.2021Hamburg339
14.12.2021Munich717
14.12.2021Berlin627
14.12.2021Hamburg5118
22.12.2021Munich127
22.12.2021Berlin147
22.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.2021
Munich753,33
Berlin454,00
Hamburg583,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)
0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Honored 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

View solution in original post

0 Kudos
2 Replies
jcarlson
MVP Honored 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
0 Kudos
DoZ
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

DoZ_0-1641378272014.png

 

This is exactly the result I needed!

Thank you ao much,

Dory

 

0 Kudos