topic Re: Create accumulative charts in Dashboards in ArcGIS Dashboards Questions

Create accumulative charts in Dashboards

JuanSthebanSanchezAragon — Fri, 20 Aug 2021 14:01:57 GMT

Hi, thanks for reading:

I´m trying to create a serial chart that allowed me to display the sum of a project, accumulating progress day by day. for example: 01/01/2021: 220 meters -> 02/01/2021: 320 (100 meters from 02/01/2021 + 220 meters from 01/01/2021), and so on. That would be really helpfull as is a really common chart in project management.

It was already asked in the community a year ago without a directa solution(https://community.esri.com/t5/arcgis-dashboards-questions/esri-dashboard-create-cumulative-chart/td-p/177802) but I´m wondering if the new arcade Dashboard expression could solve this.

thanks a lot.

Re: Create accumulative charts in Dashboards

jcarlson — Fri, 20 Aug 2021 15:27:09 GMT

Data Expressions can definitely do this. In the example below, pay attention to the for loop. In it, the original FeatureSet is filtered to all features at or before the given timestamp and summed. Thus, for each distinct date in my input FeatureSet, I will get the running total.

// Get your input layer var fs = FeatureSetByPortalItem(Portal('https://kendall.maps.arcgis.com'), 'a405b06ae8e24f94a2768a4581b79e73', 0, ['sale_date', 'sale_amount'], false) // Filtering input to a single quarter. var sales_17q1 = Filter(fs, "sale_date < timestamp '2017-04-01' and sale_date > timestamp '2017-01-01'") // Grouping by sale date to get per-date total var sales = GroupBy(sales_17q1, 'sale_date', {name: 'total', expression: 'sale_amount', statistic: 'SUM'}) var fs_dict = { fields: [ {name:'date', type:'esriFieldTypeDate'}, {name:'running_sum', type:'esriFieldTypeDouble'}], geometryType: '', features: [] } var i = 0 for(var s in sales){ // Get all dates before or equal to date var filt_date = s['sale_date'] var running_sum_fs = Filter(sales, "sale_date <= @filt_date") // Populate dict fs_dict.features[i] = { attributes: { 'date': filt_date, 'running_sum': Sum(running_sum_fs, 'total') } } console(filt_date) console(sum(running_sum_fs, 'sale_amount')) i ++ } featureset(text(fs_dict))

Once you have it output, you can also parse the dates if you like. You can also modify the expression to group your values by different time spans.

Re: Create accumulative charts in Dashboards

JuanSthebanSanchezAragon — Mon, 23 Aug 2021 13:18:15 GMT

Hi thanks for the answer it was so accurate, I almost got it but I just can´t make it to show me the table with the data with (return FeatureSet(Text(fsDict));)

Weird thing as if I return just the for loop (return fs_dict;) the array shows the correct information. Could you help me please telling me whats wrong with my sentence please. Thank you so much.

Re: Create accumulative charts in Dashboards

JuanSthebanSanchezAragon — Thu, 02 Sep 2021 02:14:53 GMT

Hi, just in case someone gets the same problem I managed to solve it by converting the date to show in the results table to UNIX system.

Re: Create accumulative charts in Dashboards

JoshHabel — Thu, 02 Dec 2021 17:49:39 GMT

Hello,

First of all I am a beginner to all but the most basic arcade expressions so this is all brand new to me. I want to be able to create cumulative charts that sum a value and allow the data to be parsed by date. The example i'm trying here is to sum all my invoices starting from Jan 1st, 2021 going forward. I've modified the below expression which I got from the above post . Seems like it should work but all i get is a Execution Error: Error. Not very helpful for troubleshooting. Is there something i'm missing??? I feel like its probably with my filter date expression but that just a guess.

Josh

// Get your input layer
var fs = FeatureSetByPortalItem(Portal('***********'), '************', 0, ['Invoice_Date', 'Invoice_Total'], false)

// Filtering input to a single quarter.
var invoices_21 = Filter(fs, "Invoice_Date > '2021-01-01'")

// Grouping by sale date to get per-date total
var invoicing = GroupBy(invoices_21, 'Invoice_Date', {name: 'total', expression: 'Invoice_Total', statistic: 'SUM'})

var fs_dict = {
fields: [
{name:'date', type:'esriFieldTypeDate'},
{name:'running_sum', type:'esriFieldTypeDouble'}],
geometryType: '',
features: []
}

var i = 0

for(var s in invoicing){
// Get all dates before or equal to date
var filt_date = s['Invoice_Date']
var running_sum_fs = Filter(invoicing, "Invoice_Date <= @filt_date")

// Populate dict
fs_dict.features[i] = {
attributes: {
'date': filt_date,
'running_sum': Sum(running_sum_fs, 'total')
}
}

console(filt_date)
console(sum(running_sum_fs, 'Invoice_Total'))

i ++
}

return featureset(text(fs_dict))

Re: Create accumulative charts in Dashboards

AshleyHayes2 — Mon, 19 Jun 2023 15:10:35 GMT

@jcarlson and/or others. I am attempting something similar. I am tracking cumulative repairs made per date and want to graph that with a serial chart in a dashboard. For example, if 5 repairs were made on Oct-1 and 20 repairs were made on Oct-15 then the chart would show 5 on Oct-1 and 25 on Oct-15 (the sum the number of repairs on Oct-1 and Oct-15), etc. When I run the below code in the Arcade Playground the Console results look somewhat promising, though the first date is missing a total: 2021-09-30T19:00:00-05:00, 2021-10-31T19:00:00-05:00 9, 2021-12-31T18:00:00-06:00 16, 2022-01-31T18:00:00-06:00 21, etc. But, the Output is empty:

featureSet:

date | running_sum | FID

Here is the code that I am currently using. Any suggestions? Thank you!!

// Get your input layer var fs = FeatureSetByPortalItem(Portal('https://*****.maps.arcgis.com'), 'deaef816ff7448dc8fae43bca1a23418', 0, ['Actual_Completed_Date'], false); // Filtering input to dates of interest. var fuaCompleted = Filter(fs, "Actual_Completed_Date > timestamp '2020-01-01'"); // Grouping by completion date to get per-date total var completionDate = GroupBy(fuaCompleted, 'Actual_Completed_Date', {name: 'total', expression: '1', statistic: 'COUNT'}); var fs_dict = { fields: [ {name: 'date', type: 'esriFieldTypeDate'}, {name: 'running_sum', type: 'esriFieldTypeDouble'} ], geometryType: '', features: [] }; var i = 0; for (var f in completionDate) { // Get the date var filt_date = f['Actual_Completed_Date']; // Log the date for debugging console(filt_date); // Get all dates before or equal to the current date var running_sum_fs = Filter(completionDate, "Actual_Completed_Date <= @filt_date"); // Calculate the running sum var running_sum = Sum(running_sum_fs, 'total'); // Log the running sum for debugging console(running_sum); // Populate the feature set dictionary fs_dict.features[i] = { attributes: { 'date': filt_date, 'running_sum': running_sum } }; i++; } featureset(text(fs_dict));

Re: Create accumulative charts in Dashboards

AshleyHayes2 — Mon, 19 Jun 2023 22:02:22 GMT

I think this may have done it, but happy to hear suggestions for improvement. Thanks.

Re: Create accumulative charts in Dashboards

apgis16 — Tue, 04 Feb 2025 14:45:39 GMT

@jcarlson - Similar to the original post, I have fields for year_range but not sure how to make it cumulative from years past in the code, and differentiate "new" vs "old"?

Re: Create accumulative charts in Dashboards

BenGultch1 — Tue, 31 Dec 2024 20:30:39 GMT

Reviving this thread because I was able to get halfway there with what @jcarlson provided, but struggling to add another field that performs the same function but against a different date field.

I can get them to function as independent data expressions but I'm looking to get these values on the same serial chart