Data Expression: From Address field, summary counts by State

SimonJackson

My data:

Address field, that will always contain our State abbreviation:

 

 

 

 

 

What I want:

A pie chart in a dashboard, showing a count of enquiries by State.  Example below:

This will actually be used on its own, embedded into an ArcGIS Hub site.

 

With arcade in a popup, I am familiar with how to pluck out the State abbreviation from the address field:

 

 

 

 

 

 

 

 

 

 

I have been struggling to try and build a Data Expression in ArcGIS dashboards to achieve this.

I have reviewed the expression in Github, but struggling to find one that I could reverse engineer to work for my fairly simple problem.

 

Can anyone assist in helping me understand if I can achieve this with a data expression?

 

The hosted feature layer is here.

The web map is here

 

AlfredBaldenweck

The first example here Introducing Data Expressions in ArcGIS Dashboards (esri.com) , might be helpful? I can't code, so I can't try it myself. 

The example uses data that is separated by commas.

 

An alternative (and probably easier, if a little bit time intensive depending on what you already have set up) is having a separate States field.

You could add in a states layer, and then extract data to points.

Or manually create a states field and fill it out, then set up your chart based on that?

Depending on how the data is originally collected, you might already have that as a separate field anyway (e.g. like when you shop online and put in your shipping address, you have two address lines, the state, and postal code). I am assuming this is how it's set up, and then concatenated into the "Address" field.

For the pop-up, you can concatenate all parts of the address together, rather than having them in one field. That way you get a nice, clean address for the viewer, but your data is easier to manipulate and pull apart.

If you can edit the table in Excel (X-tools should be able to do this), you should be able to pretty easily edit the data quickly. It might take a little bit of work but, it would be pretty easy

The following is probably not the most efficient way to do this, but it wouldn't take too long anyway.

1. Copy your data into a new workbook just in case
2. Write a list of your States across the columns (H-L, in my example)
   1. =IF(ISNUMBER(FIND(H$2,$A3))=TRUE, H$2,"")
      (Change cell references as necessary)
   2. Drag that down the column, and across so that each of your states has this formula underneath it. (Or Select Cells, Ctrl+D, Ctrl+R)
   3. This will search the address field for the (Case-sensitive) State abbreviation and return it if present, or else a blank.
      1. You can set it up to return the full name if you want. Replace the second H$2 with "Western Australia", for example
3. In another column (F, in my example)
   1. =CONCATENATE(H3,"",I3,"",J3,"",K3,"",L3)
      (Change cell references as necessary)
   2. Drag that down the column (or Ctrl+D with the column selected).
   3. This will collect all of the states from your search columns into one column
4. Copy your Concatenated Data Column and Paste as Values Only into the States Field of your original data

If you want an "Other" Value for where no state information is entered into the original address

1. In Column G, 
   1. =IF(AND(H3="", I3="", J3="", K3="", L3=""),"Other", "")
      (Change cells as necessary)
   2. Drag down the column
   3. This will input the value of "Other" if the address has none of the States listed.
2. Add  G4,""
   to your concatenate formula, drag down the column, copy and paste as values in the State field.

I probably over-explained here, but this is a pretty fast (like 5 minutes max of set-up time) way to extract that state information out of data you already have.