Dashboards Pie Chart Expression Editor: Obtaining categorical sums of data based on non-numerical data entries

[Anonymous User]

Hello all, 

I am creating a dashboard with a web map that pulls it's data from a REST service. I do not have privileges to edit this REST service in any way, hence the need for an Arcade workaround to my problem. This means I cannot create, edit or delete any fields for this data. I have also only been coding for about a week and a half so my skills are quite narrow in scope.

With that being said. . .

The purpose of the dashboard is to show various sums of farmer's and roadside markets for my state. For one of these sums in question I want to display the sum of markets open throughout the year based on 3 categories: 

1. The sum of markets open year-round
2. The sum of markets open between 6 - 11 months in the year
3. The sum of markets open less than 6 months in the year 

Each one of these pieces of data would be represented by one slice on the pie chart to create a total of 3 slices. My dashboard has a selector that filters the data displayed on the dashboard depending on if you have just farmer's markets or roadside markets selected, or both. I would like this pie chart to reflect it's data accordingly. This shouldn't be too hard as there is a field dedicated to describing each market as a farmer's market or a roadside market. I probably can just filter the completed pie chart the same way as the other dashboard elements inside the action tab of the selector. 

My inability to create such a chart stems from this:

The data for what month(s) a market is open is organized by 'YES' and 'NO' entries in 12 separate fields for 12 months of the year for all 362 markets. The data is updated by myriads of moderately independent local groups across my state so a foolproof method of inputting data is necessary to preserve schema. 

I not only am pretty lost on what function(s) to use to categorically sum the data with the 3 criteria I listed before but also wouldn't know how to begin that expression since all of the functions I have tried can't seem to use non-numerical values as indicators. (i.e. using "YES" as criteria for a sum or count function, or something similar) At least, that's what I am picking up from trial and error. You also lose global variables functionality when using the dashboards expression editor profile which has made this much more challenging for me as my only experience with Arcade expression editing has come from editing pop-ups. I am able to use the featuresetbyportalID function to grab the data as well as just the fields I need, but I don't know where to go from there.

I found this post to be somewhat similar in scope to mine but OP realized exactly the one solution I have no option of implementing which was to create a new field for his calculation. @AdrianWelsh mentioned in a reply to that post something about exploring a different workaround than creating a new field by using Arcade expressions and this seems to be exactly what I am looking to do- just without using numerical data entries. 

Best regards to anyone concerned, 

-James

[PeterKnoop · Accepted solution]

An Arcade data expression pattern you might find helpful here is to generate a new feature set, which contains the values you need calculated from your existing feature set. Then return the new feature set for the Dashboard to consume.

In other words, creating an entirely new table (based on the data from your original table), which only exists in the context of the Dashboard, and which has the data you need for your pie chart.

Your data expression would do something like:

1. Get your current table as a feature set (i.e., var fs = FeatureSetByPortalItem(...) )
2. Create an empty dictionary from which you will construct your new feature set (e.g., var my_dict = {'fields': [{'name': 'category', 'type': 'esriFieldTypeString'},{'name': 'value', 'type': 'esriFieldTypeInteger'}],
   'geometryType': '', 'features': []}; )
3. Create some counters for your different conditions (e.g., var yeararound = 0; var halfyear = 0; var lessthanhalfyear = 0; )
4. Now loop through your existing feature set from step #1 (i.e., for (var feature in fs) {...}), and for each feature apply the appropriate logic for your data to increment the correct counter.
5. Add your counters' values as rows of data in your dictionary (e.g., my_dict.features[0] = {'attributes':{'category':'yeararound','value':yeararound}}; my_dict.features[1] = {'attributes':{'category':'halfyear','value':halfyear}}; my_dict.features[2] = {'attributes':{'category':'lessthanhalfyear','value':lessthanhalfyear}}; )
6. Convert your dictionary into a feature set (i.e., my_fs = FeatureSet(Text(my_dict)); )
7. Return your new feature set to the Dashboard (i.e., return my_fs; )
8. Configure your Dashboard widget to display the data from this new feature set as desired!

Hope that helps.

[Anonymous User]

@PeterKnoop 

Thank you so much for your input! I have a few questions:

Can you elaborate very specifically what to input for the variables in step 2? I assume 'fields' stays the same, and 'name' in the first one would be the field names for all 12 months with quotations around each and commas separating them. The rest I am uncertain about. 

Can I forgo specifying geometry? All resources on geometry I have read so far state it is optional. 

Could you please elaborate more on what a "loop" is? I sort of understand from context clues but an actual summary of what exactly it means to loop something would be appreciated. 

And finally, when creating the feature set from the dictionary you used the Text function. Couldn't you just put the dictionary variable in the parenthesis without the supplementary Text function? If not, why? 

As I stated in my original post, I am mostly a layman to coding. I understand a couple of these questions aren't exactly pertinent to my solution and more-so are general coding questions so I appreciate any answers/explanations you or anyone else would supply but understand if that isn't possible. 

Thanks again for the help! I am learning new things every day. 

-James

[PeterKnoop]

Check out the blog post, Introducing Data Expressions in ArcGIS Dashboards, which includes example code that is probably easier to read then what I typed up.

The first example, "Data that needs restructuring", illustrates the same pattern I was describing. In this example, they have implemented logic that counts up occurrences of individual values, for features that can have one or more of those values in a single attribute. It loops through each feature in the table, one-at-a-time, updating the appropriate counts. (See For loops to learn more about looping in Arcade.)

In your case, it sounds like you have twelve attributes, your months with YES/NO values, for which you need to develop logic (to use in step #4) to derive the number of occurrences of year-round, half-year, and less-than-half year markets. So in your data expression everything after the "for (var feature in fs) {"  would be different. Maybe you count up the number of YES values across all twelve attributes, and then depending on that value you get, increment the appropriate counter variable.

In step 2 you are creating a dictionary to hold the JSON definition of your eventual feature set. Note that what I provided is very similar to what is in the above example, as one is following the ArcGIS format for defining a feature set. (See the top of the Arcade function reference for FeatureSet for another example of the JSON format.)

You can think of this step as defining the data schema for the table that will hold your results. Since your goal is to have a pie chart showing the number of year-round, half-year, and less-than-half year markets, then you need a data schema that is designed to hold such data. One way to do that is to create a schema that includes a category and a count. Then in step #4, you are calculating those counts, and, in step #5, store those counts in the dictionary that will become your new feature set. 

'geometryType':'' is how you define a feature set that has no geometry. In other words, a table layer, rather than a feature layer (point/line/polygon).

The Text() function serves to convert the dictionary to a text string, which is the type that FeatureSet() requires for its input parameter. If you provided the dictionary directly, then you would get a type mismatch error.

Good luck!

[Anonymous User]

This is such great information. I have ran into the error you mentioned regarding the FeatureSet function many times! I also really appreciate the links to those resources; I will read up on them asap. 

I follow your logic for how you constructed your solution to my problem. I think I will be able to implement your solution now as well thanks to your response to my questions. Thanks again so much! 

Take care

-James

[DavidPike]

Agree with Peter re Data Expression.