topic Re: Multiple Series Group Value chart - Data Expression in ArcGIS Dashboards Questions

Multiple Series Group Value chart - Data Expression

DonovanC — Wed, 22 Feb 2023 22:04:10 GMT

Hello,

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

Re: Multiple Series Group Value chart - Data Expression

jcarlson — Wed, 22 Feb 2023 22:33:15 GMT

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.

Re: Multiple Series Group Value chart - Data Expression

DonovanC — Wed, 22 Feb 2023 22:42:56 GMT

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

Re: Multiple Series Group Value chart - Data Expression

jcarlson — Wed, 22 Feb 2023 23:18:18 GMT

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

Re: Multiple Series Group Value chart - Data Expression

DonovanC — Thu, 23 Feb 2023 17:41:37 GMT

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?

Re: Multiple Series Group Value chart - Data Expression

jcarlson — Thu, 23 Feb 2023 21:13:53 GMT

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

Re: Multiple Series Group Value chart - Data Expression

DonovanC — Mon, 27 Feb 2023 14:45:55 GMT

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

Re: Multiple Series Group Value chart - Data Expression

DonovanC — Tue, 07 Jan 2025 20:37:36 GMT

@jcarlson

Hi Josh,

I appreciate your help on this but now Im looking to expand on the data expression and have the ability to use a Category Selector to help filter the data, in the selector there is no target field. I've looked at some other posts and tried to accomplish it and could not. I was hoping you could steer me in the right direction