Create accumulative charts in Dashboards

731
5
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 Notable 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

5 Replies
jcarlson
MVP Notable 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
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
e_cat
by
New Contributor II

Hi All, we are wanting to create somehting similar on dashboard creating an  accumulative graph for on-going inspections, so each month we can see how many assets have been inspected out of our program. The date function is being populated from the date survey field which the crews are updating using the fieldmaps app out on site, therefore a lot of the assets will currently have NULL values until the asset is inspected. However, we are struggling to create a graph to ignore all assets with NULL values. Is anyone able to shed some insight on how to do this please?

0 Kudos