Multiple Series Group Value chart - Data Expression

[DonovanC]

Hello,

I was wondering if this type of chart was possible using data expressions in Arcade. 

@jcarlson 

@JohannesLindner 

[jcarlson]

In general, yes. Data Expressions are really only limited by how complex an expression you want to write and how long you're willing to wait for it to execute.

What does the data look like, though? I don't see anything about the chart you've posted that wouldn't be doable in the out-of-the-box options. If Compliance is a field in the table, you probably don't need a Data Expression for this.

[DonovanC]

I've attached the survey123 and the schema of the data. In the form users select Satisfactory, Non-Satisfactory or N/A for four sections. The chart I posted came from an excel sheet that the business group is hoping I could replicate using dashboards. I've experimented with the out of the box solution and could not achieve the same results.

Donovan

[jcarlson · Accepted solution]

I see, so you're merging the responses from four fields. Yes, a Data Expression would be needed for this. Whenever possible, using a function like GroupBy will perform much faster than a for loop, since the server will be doing all the heavy lifting.

Here's an idea you could try. By using SQL, you can do a CASE WHEN ... THEN ... END block to convert your compliance fields to numeric values and then sum them across the 4 fields.

The output of an expression like this would be the same number of rows as the input, but with 3 numeric fields representing the number of compliance items matching a particular option. This could then be graphed out.

// get the featureset
var fs = FeatureSetByPortalItem(
    Portal('your portal url'),
    'itemid of service',
    0, // or the layer index of your desired layer, if not 0
    ['objectid', 'required_controls', 'excavation', 'cohe', 'third_party_utilities'],
    false
)

// define grouping SQL

var sat_sql = `
CASE WHEN required_controls == 'S' THEN 1 ELSE 0 END +
CASE WHEN excavation == 'S' THEN 1 ELSE 0 END +
CASE WHEN cohe == 'S' THEN 1 ELSE 0 END +
CASE WHEN 'third_party_utilities' == 'S' THEN 1 ELSE 0 END`

var nonsat_sql = `
CASE WHEN required_controls == 'NS' THEN 1 ELSE 0 END +
CASE WHEN excavation == 'NS' THEN 1 ELSE 0 END +
CASE WHEN cohe == 'NS' THEN 1 ELSE 0 END +
CASE WHEN 'third_party_utilities' == 'NS' THEN 1 ELSE 0 END`

var na_sql = `
CASE WHEN required_controls == 'N/A' THEN 1 ELSE 0 END +
CASE WHEN excavation == 'N/A' THEN 1 ELSE 0 END +
CASE WHEN cohe == 'N/A' THEN 1 ELSE 0 END +
CASE WHEN 'third_party_utilities' == 'N/A' THEN 1 ELSE 0 END`

// "group" your data by objectid, using SQL expressions to calculate new fields
return GroupBy(
    fs,
    'objectid',
    [
        {name: 'sat', expression: sat_sql, statistic: 'SUM'},
        {name: 'nonsat', expression: nonsat_sql, statistic: 'SUM'},
        {name: 'na', expression: na_sql, statistic: 'SUM'}
    ]
)

[DonovanC]

Thanks Josh this worked great, the data expression is awesome and has given me some ideas. But I'm guessing its a limitation with charts in dashboards I was hoping to have multi series grouped value chart using the date as a category field or would that be a totally different expression?

[jcarlson]

Easy enough! Just include the date field in the groupby function, and that should make it available as your category field.

[DonovanC]

Hi Josh, 

That worked perfectly as well. I'm not sure if its a limitation with the chart capabilities in Dashboards but can a Grouped Value chart display multiple series while using the date as a category.  I found when use the Features chart it ends up looking like this. Instead of showing each feature I would like to see the totals per month