topic Re: Custom chart with bars and lines in ArcGIS Dashboards Questions

Custom chart with bars and lines

PrachiPatel — Fri, 29 Jan 2021 23:04:30 GMT

I want to create a chart that looks like the screenshot below. The three demands need to be stacked bar charts and the supply information need to be lines. The value for each demand and supply is calculated for each month and is its own field in the underlying feature layer. The underlying feature layer is an output of a published survey 123 survey that uses submitter input such as project area and project users to calculate the supplies and demands in the project. Is this possible?

Re: Custom chart with bars and lines

jcarlson — Sat, 30 Jan 2021 02:30:55 GMT

A chart like that may be possible, depending on how the data is set up. Can you elaborate on the chart, though? Are those monthly values being aggregated from multiple rows in the table?

The only way I'm aware of to do aggregated values in a serial chart is to use the Grouped Values option, and you're only able to aggregate a single field. In order to reference multiple fields in a single chart, you have to use the Features option, but then you end up with a separate set of bars for ever row in your table. Fields can aggregate multiple fields, but not separate them by the date.

In order to accomplish the chart you have pictured there, you'd need a separate table with a row for each month, and have the aggregated values populating the fields. Then you'd be able to use the Features option and achieve what you're going for.

Re: Custom chart with bars and lines

PrachiPatel — Mon, 15 Mar 2021 17:17:58 GMT

Hi Josh, you are right about the required format of the table with months in rows and the water supply/demand values in columns. In the xls form. however, the value of each month for each kind of water supply/demand is calculated separately and therefore is its own field. I am struggling with how to get the values in the format I need.

One thing I tried was create a separate survey using repeat , filled it manually and it spat out the table in the format I need. However, I need the process to run automatically and haven't quite figured out how to make the repeat function run automatically. Would appreciate any suggestions

Re: Custom chart with bars and lines

jcarlson — Mon, 15 Mar 2021 17:28:42 GMT

I think this could still be doable with a separate standalone table, but it would have to be done outside of Survey123. Imagine a layer like this:

Month | Potable | Stormwater | etc. | etc.
------|---------|------------|------|-----
Jan   |     1000|         900|   250|   25  
Feb   |     1250|        1100|   100|   33

You could have an automated process pull the values from the main survey dataset, aggregate each value by month, then submit the values to the table. Point the serial chart at that table, and you can symbolize it the way you want.

Re: Custom chart with bars and lines

PrachiPatel — Mon, 15 Mar 2021 22:20:20 GMT

Yes, After reading your previous response , I created a table to do so and can create a chart, that is not stacked, but it a combination of lines and bars. Now I need to figure out how to automate the creation of that table because I want the dashboard to update every time a submitter hits submit on the survey 123 survey. Figuring the automation out.

Re: Custom chart with bars and lines

jcarlson — Tue, 16 Mar 2021 01:48:53 GMT

Glad to hear it! The ArcGIS Python API and Pandas will be your friends for the automation portion. We do a similar process for updating a few standalone tables, and here's what it looks like, roughly:

from arcgis import GIS gis = GIS('portal-url', 'user', 'password') # The survey survey_layer = gis.content.get('survey-itemID').layers[0] # The table chart_table = gis.content.get('chart-table-itemID').tables[0] # Query the survey layer to a dataframe all_the_data = survey_layer.query(as_df=True) # Do whatever aggregating / reshaping you need to # This section will look different depending on what you're doing agg_data = all_the_data.groupby('grouping_field').sum() # Truncate and append table records chart_table.manager.truncate() chart_table.edit_features(adds=agg_data.spatial.to_featureset())

That should be general enough to adapt to your particular needs. Then just get that script called up with a Task Scheduler to run at regular intervals and you'll never have to think about it again!

Also, since Survey tends to have editor tracking fields present by default, you could pre-query the layer to see if any new submissions have been added since the last script run, and just skip the whole process if there is no new data.

Re: Custom chart with bars and lines

Aubrey_Drescher — Tue, 13 Apr 2021 15:19:02 GMT

Thanks for this solution, @jcarlson! I was able to work with @PrachiPatel to write a script using the template you provided.

Instead of setting up a recurring task, I stored it as a Google Cloud Function and then added the URL of the function as a webhook that runs when the survey is submitted. There's a blog post that explains doing this here: Calling Python scripts with Survey123, Integromat,... - Esri Community.

I used this code to get the object id of the submitted survey.

def update_table(request): request_json = request.get_json(silent=True) object_id = request_json['feature']['result']['objectId']

Then, instead of truncating and recreating the entire chart_table, I filtered the survey_layer by the object id, did my reshaping on just the submitted row, and added the reshaped rows to the chart_table.

survey_data = survey_layer.query(where='objectid = ' + str(object_id), as_df=True) # In this section (omitted) the submitted row gets pivoted to 12 rows (one per month) chart_table.edit_features(adds=pivoted_data.spatial.to_featureset())

Re: Custom chart with bars and lines

jcarlson — Tue, 13 Apr 2021 17:32:30 GMT

That's fantastic! I may end up using that in a future project, so thank you for documenting it so well!

Re: Custom chart with bars and lines

JanetBrewster1 — Mon, 17 Jun 2024 20:19:55 GMT

@PrachiPatel Were you able to figure out to create a serial chart in Dashboards with BOTH bars and lines? I have not been able to do this, even with aggregated data. I would appreciate any insights if you made this happen. Thank you!

Janet

Re: Custom chart with bars and lines

PrachiPatel — Wed, 10 Jul 2024 01:57:43 GMT

Hi @JanetBrewster1 , The chart I was able to make has bars and lines, but the bars represent a single field instead of a stacked bar representing multiple fields. So instead of stacking the bars with data from 4 types of demand, I just have a single bar showing non-potable demand only per month, and then the lines show various non potable supplies per month.