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!

SerialChartExample.PNG

Almaz_Naizghi_0-1735922578094.png

jcarlson

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']}},
    ]
}))