Create accumulative charts in Dashboards

2414
6
Jump to solution
08-20-2021 07:01 AM
JuanSthebanSanchezAragon
New Contributor II

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-...) but I´m wondering if the new arcade Dashboard expression could solve this.grafica de avance acumulado.JPG

thanks a lot.

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

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.

jcarlson_0-1629473237053.png

 

- Josh Carlson
Kendall County GIS

View solution in original post

6 Replies
jcarlson
MVP Esteemed Contributor

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.

jcarlson_0-1629473237053.png

 

- Josh Carlson
Kendall County GIS
JuanSthebanSanchezAragon
New Contributor II

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.

arrays.JPG

Array.JPG

  

0 Kudos
JuanSthebanSanchezAragon
New Contributor II

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.

0 Kudos
AshleyHayes2
New Contributor III

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

 

 

 

0 Kudos
AshleyHayes2
New Contributor III

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);
0 Kudos
JoshHabel
Occasional Contributor

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

0 Kudos