Creating a Serial Chart in Dashboard using Arcade 'count' on multiple fields

[ttw_marine]

I want to create a Serial Chart for a Dashboard where each column corresponds to a field in a single feature class. I think this needs to be done with Arcade. I've been trying to write something but not very familiar with Arcade. Does anyone know what the code might look like?

There are 4 fields, I would like each to be a bar on the chart.

Each field's entries are equal to either "YES" or "NO".

For each bar & field, I would like a count of all the "YES" entries and all the "NO" entries.

I would like this represented as a stacked bar for each field.

I have included an image giving a very rough example. Any ideas very welcome, thanks!

[jcarlson · Accepted solution]

Yes, unfortunately a chart's "fields" option only works for numeric fields. Summing multiple fields is easy enough in a single GroupBy expression. But getting the yes and no sums to stack, that's another thing.

You still don't need to do a loop, but GroupBy only gets us 95% of the way there. Taken those totals and building our own FeatureSet does the trick.

var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  'itemid of service',
  0, // layer index
  ['A', 'B', 'C', 'D'],
  false
)

var g = First(GroupBy(
    fs,
    {name: 'result', expression: '1'},
    [
        {name: 'AY', expression: "CASE WHEN A = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'AN', expression: "CASE WHEN A = 'N' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'BY', expression: "CASE WHEN B = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'BN', expression: "CASE WHEN B = 'N' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'CY', expression: "CASE WHEN C = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'CN', expression: "CASE WHEN C = 'N' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'DY', expression: "CASE WHEN D = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'DN', expression: "CASE WHEN D = 'N' THEN 1 ELSE 0 END", statistic: 'SUM'},
    ]
))

return FeatureSet(Text({
    fields: [
        {name: 'letter', type: 'esriFieldTypeString'},
        {name: 'response', type: 'esriFieldTypeString'},
        {name: 'count', type: 'esriFieldTypeInteger'},
    ],
    geometryType: '',
    features: [
        {attributes: {letter: 'A', response: 'Y', count: g['AY']}},
        {attributes: {letter: 'A', response: 'N', count: g['AN']}},
        {attributes: {letter: 'B', response: 'Y', count: g['BY']}},
        {attributes: {letter: 'B', response: 'N', count: g['BN']}},
        {attributes: {letter: 'C', response: 'Y', count: g['CY']}},
        {attributes: {letter: 'C', response: 'N', count: g['CN']}},
        {attributes: {letter: 'D', response: 'Y', count: g['DY']}},
        {attributes: {letter: 'D', response: 'N', count: g['DN']}},
    ]
}))

[ttw_marine]

Thank you Josh this looks great! Really appreciate your help with this.

I've put in this code and substituted the actual layer & field names, but when I run the script I get the following error: "Test execution error: Invalid variable assignment.. Verify test data.". Not sure if I've inputted anything in wrong, or perhaps its an issue with the 'portal' URL?

[ttw_marine]

Just tried it again this morning and it's working! Exactly what I was after, thanks Josh!