Dynamic Serial Chart

[AaronThairJorgenson]

I am having an issue in Dashboard.

I would like to create a Serial Chart (bar graph) for a stream (line vector) layer.

Each stream segment (record) has 8 different fields that I want included in this graph.

7 fields representing fish species and one representing stream miles. (See example).

Chinook	Chum	Coho	Steelhead	Cutthroat	Sculpin	Stream Miles
Documented	Documented	Documented	Documented	Documented		0.4
	Documented	Documented		Documented	Documented	0.6
		Documented		Documented	Documented	0.8

In this case I would like the first bar to represent the sum of stream miles for all records with documented Chinook. The second bar would represent the sum of stream miles for all records with documented Chum…. And so on.

This does not seem possible, I have been trying multiple ways and I am still failing.

Does anyone have a suggestion?

[AndreasHall]

I think you would need to some data preprocessing/engineering and change the data schema to support what you want to do in Serial Chart. This might be possible to achieve with Arcade by creating a data expression and using that as input to your serial chart (Create a data expression—ArcGIS Dashboards | Documentation). I´m not an expert on Arcade so I cannot advise on how to create the expression. Otherwise, you would need to create a new feature layer that has the needed data schema.

[jcarlson]

A Data Expression might be able to help, along with a Distinct statement. Basically, we will replace "Documented" with the stream miles, then the chart can just show a sum of those fields. By including the objectID in the fields list, we ensure that the output has the same number of rows as the input, effectively giving us access to SQL field calculations for the output.

var fs = FeatureSetByPortalItem(
    Portal('your url'),
    'itemid of feature service',
    0, // or whatever layer index
    ['fields', 'you', 'need'], // but since we're just using Distinct, you maybe don't need these
    false
)

return Distinct(
    fs,
    [
        {name: 'objectid', expression: 'objectid'},
        {name: 'chinook_miles', expression: "CASE WHEN chinook = 'Documented' THEN stream_miles ELSE 0 END"},
        {name: 'chum_miles', expression: "CASE WHEN chum = 'Documented' THEN stream_miles ELSE 0 END"}
        // and so on
    ]
)

[AaronThairJorgenson]

Thank you!

For

    0, // or whatever layer index

 I am a little confused, not sure what to do here. Any clarification would be much appreciated!

I am also a little confused with

['fields', 'you', 'need']

For this do I use

['Chinook', 'Chum', 'Stream_Miles']

Thank you so much for your help! This is all new to me (and kind of fun to learn)

[jcarlson]

"//" denotes the following text to be a comment. The number is the only important part. If your service has a single layer, it will almost always be 0. If your service has more than one layer in it, the number will correspond to a specific layer. You can check the REST URL to see what the layer index actually is, or look at the end of the URL on the item's Overview page.

As for the fields, it's literally just an array of the fields you need. But try leaving it empty (that's "[]"), actually. I think the expression will still work.

In Data Expression fields need to be explicitly requested from the server to work with, so if you needed the stream miles and 7 fish species, you'd need to ask for those 8 fields. But the Distinct function sends a command to the Feature Service, so it doesn't necessarily need the fields in the FeatureSet when it makes the request, which is why maybe you can leave this part of the expression and an empty array.

[AaronThairJorgenson]

Thank you! You are incredibly helpful!

It is working. When I run "test" the resulting table is populated with what appears to be the correct numbers!

Unfortunately when I build the table, adding in all the fields and changing the statistics to sum the graph is flat. (see image)

Any idea why this is happening?

I tried it with and without the

This is what my script looks like.

var fs = FeatureSetByPortalItem(
Portal('https://uwt-gis-geotech.maps.arcgis.com'),
'9b9856295f3943329bc225d0d7e8962f',
49,
['Chinook_Status', 'Chum_Status','Coho_Status', 'Mykiss_Status', 'Cutthroat_Status', 'Lamprey_Status', 'Sculpin_Status', 'Stream_Lenght_Miles'],
false
)

return Distinct(
fs,
[
{name: 'objectid', expression: 'objectid'},
{name: 'Chinook_Miles', expression: "CASE WHEN Chinook_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Chum_Miles', expression: "CASE WHEN Chum_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Coho_Miles', expression: "CASE WHEN Coho_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Mykiss_Miles', expression: "CASE WHEN Mykiss_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Cutthroat_Miles', expression: "CASE WHEN Cutthroat_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Lamprey_Miles', expression: "CASE WHEN Lamprey_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
{name: 'Sculpin_Miles', expression: "CASE WHEN Sculpin_Status = 'Documented' THEN Stream_Lenght_Miles ELSE 0 END"},
]
)