How to sum all records with arcade expressions

8105
10
Jump to solution
03-21-2018 09:52 AM
Cesar_AugustoRodriguez
New Contributor III

Hi! I'm working with arcade expressions and I need to summarize all values of all records that I have in a layer to asign a symbology.

Example:

IDValue
1100
1200
2300
2400

I need sum (100 + 200 + 300 + 400) = 1000 of the same field, if I'll make a filter over ID = 1 I'll have sum(100 + 200) = 300

When I use the functon Sum() this only sum two o more fields but It doesn't sum all records of the same field

someone could help me with a insights how I can do it

Thank you so much

Regards

0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

Hi Joe Head , to start with the support of Arcade in Operations Dashboard, if you look at this help topic: What's new?—Operations Dashboard for ArcGIS | ArcGIS you will see:

Use better maps, with support for smart mapping, heat map renderers, vector basemaps, stream layers, and labels. You can also use Arcade expressions to enhance map labels and symbology, as well as create custom pop-up content.

... and in this topic: Operation views—Operations Dashboard for ArcGIS | ArcGIS it states:

More of the modern capabilities of the ArcGIS platform are supported in dashboards. This includes labels, smart mapping, vector basemaps, stream layers, heat map renderers, and Arcade expressions.

So, Arcade expressions are supported in the Operations Dashboard. I will dig in a little further if these virtual fields created with Arcade expressions are available to use in the widgets.

As for your use case, I understand what you are after. Have a look at the Arcade snippet below:

var elements = Round(Random() * 3 + 2.5, 0);
Console("elements:" + elements);

// mimic getting cost values from multiple features
var dct_status_cost = {};
for (var i = 0; i < elements; i++) {
    Console("i:" + i);
    var cost = Random() * 100.0 + 50.0;
    Console("cost:" + cost);
    var status = Round(Random() * 3 + 0.5, 0);
    Console("status:" + status);
    var key = Text(i);
    Console("key:" + key);
    dct_status_cost[key] = [status, cost];
}

// now read the list of costs and calculate the total
var total_cost = 0;
for (var i = 0; i < elements; i++) {
    Console("i:" + i);
    key = Text(i);
    var data = dct_status_cost[key];
    status = data[0];
    Console("status:" + status);    
    cost = data[1];
    Console("cost:" + cost);
    var cost_factor = status / 10.0 + 0.1;
    Console("cost_factor:" + cost_factor);    
    Console("cost_factor * cost:" + cost_factor * cost);
    total_cost += cost_factor * cost;
    Console("total_cost:" + total_cost);
}

return total_cost;

The first part is to mimic the part of getting values from multiple features (which is not possible yet in Arcade). The second part calculated the sum of the cost including the influence of the status on the value.

The Console statements write text to the console. Here is what is written to the console:

elements:4

i:0
cost:131.6079845894106
status:1
key:0

i:1
cost:103.36961796067972
status:3
key:1

i:2
cost:111.06575191076075
status:3
key:2

i:3
cost:127.81189378433866
status:3
key:3


i:0
status:1
cost:131.6079845894106
cost_factor:0.2
cost_factor * cost:26.32159691788212
total_cost:26.32159691788212

i:1
status:3
cost:103.36961796067972
cost_factor:0.4
cost_factor * cost:41.34784718427189
total_cost:67.66944410215402

i:2
status:3
cost:111.06575191076075
cost_factor:0.4
cost_factor * cost:44.4263007643043
total_cost:112.09574486645832

i:3
status:3
cost:127.81189378433866
cost_factor:0.4
cost_factor * cost:51.12475751373547
total_cost:163.22050238019378‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In the case described by Cesar only those features with a specific ID will need to be retrieved to define the output value of the expression. This is a single request and is something that could be done. In your case for each value possibly a full table scan is required which would be killing on performance.  

View solution in original post

10 Replies
Lake_Worth_BeachAdmin
Occasional Contributor III

posting here to get notifications of replies, I am interested in this also

XanderBakker
Esri Esteemed Contributor

At this moment it is not possible to get statistics over multiple records. This functionality may be introduced in the future, but you need to take into account that this will come with a cost. Arcade allow for the generation of dynamic results, and with each zoom or pan these statistics will have to be recalculated. 

Would be interesting to know a little more about the specific use cases to see if it is possible to resolve this in a different way.

crodriguezesri-co-esridist , si quieres me llames o escribes el lunes y hablamos para indagar que es posible.

Lake_Worth_BeachAdmin
Occasional Contributor III

Here is my case:

I have a series of point features in the same feature class. Each point has a attribute "cost" and "status".

I would like to sum the total cost by the following:

if $feature.status == 1 then : $feature.cost * .20

if $feature.status == 2 then: $feature.cost * .30

if $feature.status == 3 then: $feature.cost * .40

return sum of all features form the expression above

( I would be displaying this in Op. Dashboard) which I dont think yet supports arcade expressions, in a widget... I.E a widget that would update the total cost $ based on features being assigned a status)

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Joe Head , to start with the support of Arcade in Operations Dashboard, if you look at this help topic: What's new?—Operations Dashboard for ArcGIS | ArcGIS you will see:

Use better maps, with support for smart mapping, heat map renderers, vector basemaps, stream layers, and labels. You can also use Arcade expressions to enhance map labels and symbology, as well as create custom pop-up content.

... and in this topic: Operation views—Operations Dashboard for ArcGIS | ArcGIS it states:

More of the modern capabilities of the ArcGIS platform are supported in dashboards. This includes labels, smart mapping, vector basemaps, stream layers, heat map renderers, and Arcade expressions.

So, Arcade expressions are supported in the Operations Dashboard. I will dig in a little further if these virtual fields created with Arcade expressions are available to use in the widgets.

As for your use case, I understand what you are after. Have a look at the Arcade snippet below:

var elements = Round(Random() * 3 + 2.5, 0);
Console("elements:" + elements);

// mimic getting cost values from multiple features
var dct_status_cost = {};
for (var i = 0; i < elements; i++) {
    Console("i:" + i);
    var cost = Random() * 100.0 + 50.0;
    Console("cost:" + cost);
    var status = Round(Random() * 3 + 0.5, 0);
    Console("status:" + status);
    var key = Text(i);
    Console("key:" + key);
    dct_status_cost[key] = [status, cost];
}

// now read the list of costs and calculate the total
var total_cost = 0;
for (var i = 0; i < elements; i++) {
    Console("i:" + i);
    key = Text(i);
    var data = dct_status_cost[key];
    status = data[0];
    Console("status:" + status);    
    cost = data[1];
    Console("cost:" + cost);
    var cost_factor = status / 10.0 + 0.1;
    Console("cost_factor:" + cost_factor);    
    Console("cost_factor * cost:" + cost_factor * cost);
    total_cost += cost_factor * cost;
    Console("total_cost:" + total_cost);
}

return total_cost;

The first part is to mimic the part of getting values from multiple features (which is not possible yet in Arcade). The second part calculated the sum of the cost including the influence of the status on the value.

The Console statements write text to the console. Here is what is written to the console:

elements:4

i:0
cost:131.6079845894106
status:1
key:0

i:1
cost:103.36961796067972
status:3
key:1

i:2
cost:111.06575191076075
status:3
key:2

i:3
cost:127.81189378433866
status:3
key:3


i:0
status:1
cost:131.6079845894106
cost_factor:0.2
cost_factor * cost:26.32159691788212
total_cost:26.32159691788212

i:1
status:3
cost:103.36961796067972
cost_factor:0.4
cost_factor * cost:41.34784718427189
total_cost:67.66944410215402

i:2
status:3
cost:111.06575191076075
cost_factor:0.4
cost_factor * cost:44.4263007643043
total_cost:112.09574486645832

i:3
status:3
cost:127.81189378433866
cost_factor:0.4
cost_factor * cost:51.12475751373547
total_cost:163.22050238019378‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In the case described by Cesar only those features with a specific ID will need to be retrieved to define the output value of the expression. This is a single request and is something that could be done. In your case for each value possibly a full table scan is required which would be killing on performance.  

Lake_Worth_BeachAdmin
Occasional Contributor III

Thank you for the detailed reply!

What I meant for Op. Dashboard was Arcade Expressions were not supported in the widgets

I hope the ability to scan(read) an entire layer or multiple layers to preform calculations can be implemented in the future, this would greatly extend AGOL possibilities.

AnninaHirschi_Wyss1
Occasional Contributor III
0 Kudos
Lake_Worth_BeachAdmin
Occasional Contributor III

No I did not, I thought it was already planned implementation...?

0 Kudos
AnninaHirschi_Wyss1
Occasional Contributor III

Hi Joe,

Arcade attributes are now displayed in the Dashboard's pop ups and symbology, but they are not accessible e.g. for creating charts. I don't know if there are plans for that, best to ask the question (or move this thread) in the Operations Dashboard for ArcGIS‌ space.

operations dashboard‌

arcade expressions‌

BenBaker1
Occasional Contributor

Is there an alternate way of accomplishing this now that Arcade is better supported in Dashboards?

Specifically, I'm wanting to calculate an average to display in a Dashboard Indicator to show days to completion that would be calculated from 'days' in the expression below:

var startDt = $feature.SubmitDt;
var endDt = $feature.CompletedDt;
var status = $feature.Status;

var days = Round(DateDiff(endDt, startDt, 'days'), 2);
if (status == 'wo_completed'){
    return days
}