nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Create accumulative charts in Dashboards

JuanSthebanSanchezAragon

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. grafica de avance acumulado.JPG

thanks a lot.

Encontre mais publicações marcadas com

ArcGIS Online

Accepted answers

jcarlson

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.

All comments

BenGultch1

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

apgis16

@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"?

AshleyHayes2

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

var fs = FeatureSetByPortalItem(Portal('https://******.maps.arcgis.com'), 'deaef816ff7448dc8fae43bca1a23418', 0, ['Actual_Completed_Date'], false);

var fuaCompleted = Filter(fs, "Actual_Completed_Date > timestamp '2020-01-01'");

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' }
    ],
    features: []
};

var i = 0;

for (var f in completionDate) {
    var filt_date = f['Actual_Completed_Date'];
    var running_sum_fs = Filter(completionDate, "Actual_Completed_Date <= @filt_date");
    var running_sum = Sum(running_sum_fs, 'total');
    
    fs_dict.features[i] = {
        attributes: {
            'date': filt_date,
            'running_sum': running_sum
        }
    };
    
    i++;
}

return featureset(fs_dict);

AshleyHayes2

@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));

JoshHabel

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))

JuanSthebanSanchezAragon

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.

JuanSthebanSanchezAragon

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));)

Empty table.JPG

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.

Arcade Acumulative charts.txt

jcarlson

// 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.

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.