Cumulative Chart With Arcade Expression

[JoshHabel]

Hello,

I am having trouble creating a cumulative chart for my dashboard using a modified arcade expression (see below).   It doesn't give me an error or anything it just spits out this empty feature set.  Any help would be appreciated!

Josh

var portal = Portal("https://www.arcgis.com/");
var vendor = GroupBy(
FeatureSetByPortalItem(portal,"********************",0,["*"],false),
["Invoice_Date"],
[
{ name: "Invoice_Total", expression: "Invoice_Total", statistic: "SUM" }
]
);

var combinedDict = {
fields: [
{ name: "date1", type: "esriFieldTypeDate" },
{ name: "cost1", type: "esriFieldTypeDouble" },
],
geometryType: "",
features: [],
};

var i = 0;
// Loop through each FeatureSet and store its attributes
for (var v in vendor) {
combinedDict.features[i++] = {
attributes: {
date1: "Invoice_Date",
cost1: v["Invoice_Total"],
},
};
}

//return combinedDict;
// Return dictionary cast as a FeatureSet
return FeatureSet(Text(combinedDict));

[jcarlson]

If you look at the example expressions, calling combinedDict.features[i] should be separate from i++.

Try putting i++ on its own line at the end of your for loop, and reference the index i, not i++ when you are adding to the combinedDict.features array.

Alternatively, you could use Push to populate an array, then supply that array when you create the combinedDict object.

var features = []

for (var v in vendor){
    var feat = {
        attributes: {
            date1: v["Invoice_Date"],
            cost1: v["Invoice_Total"]
        }
    }
    Push(features, feat)
};

var combinedDict = {
    fields: [
        { name: "date1", type: "esriFieldTypeDate" },
        { name: "cost1", type: "esriFieldTypeDouble" },
    ],
    geometryType: "",
    features: features,
};

[JohannesLindner]

• you don't use quotation marks around the dictionary keys
• you try to store the string "Invoice_Date" instead of the value of the field Invoice_Date (even if you used the quotation marks correctly, this would get rejected, as the type of date1 is esriFieldTypeDate)

var portal = Portal("https://www.arcgis.com/")
var vendor = GroupBy(
  FeatureSetByPortalItem(portal,"********************",0,["*"],false),
  ["Invoice_Date"],
  [
    { "name": "Invoice_Total", "expression": "Invoice_Total", "statistic": "SUM" }
  ]
)

var combinedDict = {
  "fields": [
    { "name": "date1", "type": "esriFieldTypeDate" },
    { "name": "cost1", "type": "esriFieldTypeDouble" },
  ],
  "geometryType": "",
  "features": []
}

var i = 0
// Loop through each FeatureSet and store its attributes
for (var v in vendor) {
  combinedDict.features[i++] = {
    "attributes": {
      "date1": v["Invoice_Date"],
      "cost1": v["Invoice_Total"]
    }
  }
}

//return combinedDict;
// Return dictionary cast as a FeatureSet
return FeatureSet(Text(combinedDict))

I don't think you need the combinedDict at all. All you're doing is copy the rows from vendor verbatim. If you're not planning to actually combine vendor with another FeatureSet, this should be enough:

var portal = Portal("https://www.arcgis.com/")
var vendor = GroupBy(
  FeatureSetByPortalItem(portal,"********************",0,["*"],false),
  ["Invoice_Date"],
  [
    { "name": "Invoice_Total", "expression": "Invoice_Total", "statistic": "SUM" }
  ]
)
return vendor

[jcarlson]

Good catch! FWIW, you can totally omit quotes on the dict keys and Arcade has no problem with it.

[JohannesLindner]

Ah OK, thanks for the tip. I was wondering why that wouldn't throw an error. Seems to be a Javascript concept.

I don't know how to feel about this, seems kinda dirty to me. Although I really like the reverse, where you just call dict.key instead of the pythonic dict["key"]. Seems like Arcade handles dictionaries like duck typed objects in Python.

[JoshHabel]

I really appreciate the help! That did get rid of the error for me but unfortunately my expression doesn't give me a running sum total like i thought it was going to.   I don't have much experience writing these types of expressions so its a bit of searching around trying this and that.

Josh

[JohannesLindner · Accepted solution]

So you want something like this?

var portal = Portal("https://www.arcgis.com/")
var vendor = GroupBy(
  FeatureSetByPortalItem(portal,"********************",0,["*"],false),
  ["Invoice_Date"],
  [
    { "name": "Invoice_Total", "expression": "Invoice_Total", "statistic": "SUM" }
  ]
)

var combinedDict = {
  "fields": [
    { "name": "date1", "type": "esriFieldTypeDate" },
    { "name": "cost1", "type": "esriFieldTypeDouble" },
    { "name": "running_sum1", "type": "esriFieldTypeDouble" },
  ],
  "geometryType": "",
  "features": []
}

var i = 0
var running_sum = 0
for (var v in vendor) {
  running_sum += v["Invoice_Total"]  // increase the running sum
  combinedDict.features[i++] = {
    "attributes": {
      "date1": v["Invoice_Date"],
      "cost1": v["Invoice_Total"],
      "running_sum1": running_sum 
    }
  }
}

//return combinedDict;
// Return dictionary cast as a FeatureSet
return FeatureSet(Text(combinedDict))

[JoshHabel]

Johannes,

Tried your expression above and it worked great!  See below... Really appreciate the help!!

Have a good holiday!

Josh

[jcarlson]

Alternate method: https://github.com/jdcarls2/arcade-expressions/blob/running-total/dashboard_data/RunningTotal(Serial... Your post has inspired me to finish this script and submit a PR, so thanks!

@JohannesLindnermay have a quicker solution here, though.