Create accumulative charts in Dashboards

369
3
Jump to solution
08-20-2021 07:01 AM
Labels (1)
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 Regular 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

3 Replies
jcarlson
MVP Regular 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

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