topic ArcGIS dashboards Data Expression including Rank in ArcGIS Dashboards Questions

ArcGIS dashboards Data Expression including Rank

LJackson29 — Mon, 23 Sep 2024 18:06:06 GMT

Hello -

I have created the below data expression in the code box to select the fields I need from the source layer and it works but I want to create a new variable for rank which will rank the field PR_EXHYDPAR (double) after ordering it. I can use the below code in Pro to calculate rank, but cannot figure out how to incorporate it into the Dashboard Data Expression. I would prefer to do this on the fly than add additional fields to my layer which would have to be updated regularly when the data is updated (There are 7 fields I need to create ranks for). Can anyone provide guidance on how to do this?

var setfeatures = FeatureSetByName($datastore, "SNP_Project");

var ordered = OrderBy(setfeatures, 'PR_EXHYPDAR DESC')

var rank = 0
for(var f in ordered) {
rank++
if(f.OBJECTID == $feature.OBJECTID) {
return rank
}
}

var port = Portal('https://www.arcgis.com'); var fs = filter(FeatureSetByPortalItem( port,'01610f3a8912467c8b06f4b223a6b5e1', 0, ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_ORIGTOTCOST', 'PR_P6TOTCOST', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTNOMSCORE', 'PR_TOTSCORE', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_MODELDATE', 'PR_PARTOOLDATE', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_STRPARDATE'], false), "PR_SWIM_ONLY='No' AND PR_PART_ONLY='No' AND PR_NONDISTRICT='No' AND (PR_STATUS='01. Submitted – Pending Review/Scoring' OR PR_STATUS='02. Scored' OR PR_STATUS='03. Advanced SW Planning' OR PR_STATUS='04. Design' OR PR_STATUS='05. Construction' OR PR_STATUS='07. Completed')" ); var out_dict = { fields: [ {name: 'projectid', type: 'esriFieldTypeString'}, {name: 'projname', type: 'esriFieldTypeString'}, {name: 'status', type: 'esriFieldTypeString'}, {name: 'origtotcost', type: 'esriFieldTypeDouble'}, {name: 'P6totcost', type: 'esriFieldTypeDouble'}, {name: 'exhydpar', type: 'esriFieldTypeDouble'}, {name: 'exstrpar', type: 'esriFieldTypeDouble'}, {name: 'extotpar', type: 'esriFieldTypeDouble'}, {name: 'althydpar', type: 'esriFieldTypeDouble'}, {name: 'altstrpar', type: 'esriFieldTypeDouble'}, {name: 'alttotpar', type: 'esriFieldTypeDouble'}, {name: 'redhydpar', type: 'esriFieldTypeDouble'}, {name: 'redstrpar', type: 'esriFieldTypeDouble'}, {name: 'redtotpar', type: 'esriFieldTypeDouble'}, {name: 'totnomscore', type: 'esriFieldTypeInteger'}, {name: 'totscore', type: 'esriFieldTypeDouble'}, {name: 'wac', type: 'esriFieldTypeString'}, {name: 'subwatershed', type: 'esriFieldTypeString'}, {name: 'community1', type: 'esriFieldTypeString'}, {name: 'modeldate', type: 'esriFieldTypeDate'}, {name: 'partooldate', type: 'esriFieldTypeDate'}, {name: 'strpardate', type: 'esriFieldTypeDate'}, {name: 'swimonly', type: 'esriFieldTypeString'}, {name: 'partonly', type: 'esriFieldTypeString'}, {name: 'nondistr', type: 'esriFieldTypeString'}, ], geometryType: '', features: [] } // Iterate over each feature in the layer for (var f in fs){ Push( out_dict['features'], {attributes: { projectid: Text(f['PROJECTID']), projname: Text(f['PR_PROJNAME']), status: Text(f['PR_STATUS']), origtotcost: Number(f['PR_ORIGTOTCOST']), P6totcost: Number(f['PR_P6TOTCOST']), exhydpars: Number(f['PR_EXHYDPAR']), exstrpar: Number(f['PR_EXSTRPAR']), extotpar: Number(f['PR_EXTOTPAR']), althydpar: Number(f['PR_ALTHYDPAR']), altstrpar: Number(f['PR_ALTSTRPAR']), alttotpar: Number(f['PR_ALTTOTPAR']), redhydpar: Number(f['PR_REDHYDPAR']), redstrpar: Number(f['PR_REDSTRPAR']), redtotpar: Number(f['PR_REDTOTPAR']), totnomscore: Number(f['PR_TOTNOMSCORE']), totscore: Number(f['PR_TOTSCORE']), wac: Text(f['PR_WAC']), subwatershed: Text(f['PR_SUBWATERSHED']), community1: Text(f['PR_COMMUNITY1']), modeldate: Date(f['PR_MODELDATE']), partooldate: Date(f['PR_PARTOOLDATE']), strpardate: Date(f['PR_STRPARDATE']), swimonly: Text(f['PR_SWIM_ONLY']), partonly: Text(f['PR_PART_ONLY']), nondistr: Text(f['PR_NONDISTRICT']), } } ) } //Return populated out_dict as FeatureSet var par = FeatureSet(Text(out_dict)) return par

Re: ArcGIS dashboards Data Expression including Rank

jcarlson — Mon, 23 Sep 2024 19:40:07 GMT

Sorting and looping through would work with a single field, but even that can take a while to execute. Adding multiple fields, well, that will be a really long-running expression.

There may be a better way to approach this, but it will still result in tons of queries to the server. You could try using a custom function to "memorize" the FeatureSet first, which will speed things up a lot. (https://www.jcarlson.page/posts/dashboards-memorize/)

To get the rank of each field in a single loop, we just need some filtering. You can do a filter and see how many features are greater/less than the current feature, and that's basically the rank. Maybe add 1 to it? The smallest value in a field will have no features less than it, so the filtered count + 1 = 1, that's it's rank.

Since we're doing this repeatedly for multiple fields we could use another custom function for the rank.

Here's an example, just swap in your values as needed:

Re: ArcGIS dashboards Data Expression including Rank

jcarlson — Mon, 23 Sep 2024 19:41:27 GMT

I forgot to add 1 to the rank! Just change line 27 to be return Count(filt_set)+1 if you want that. I don't really mind it being zero-indexed, as long as it's correctly calculating the ranks.

Re: ArcGIS dashboards Data Expression including Rank

LJackson29 — Mon, 23 Sep 2024 21:06:47 GMT

@jcarlson Thanks for your help on this. I can't seem to get it to run - surely user error. See the code in the code block with my questions, and let me know where you think I am going wrong. Many thanks!

//Memorize function function Memorize(fs) { // is this saying use the schema from fs identified below including fields, geometry and features? var temp_dict = { fields: Schema(fs)['fields'], geometryType: '', features: [] } // what is this section doing? for (var f in fs) { var attrs = {} for (var attr in f) { attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]) } Push( temp_dict['features'], {attributes: attrs} ) } return FeatureSet(Text(temp_dict)) } //How does this code identify the lowest value and starts there? //Rank Function function getRank(feat, field, set){ var filt_val = feat[field] var filt_set = Filter(set, '${field} < @filt_val') return Count(filt_set)+1 } var fs = Memorize(filter(FeatureSetByPortalItem( portal,'01610f3a8912467c8b06f4b223a6b5e1', 0, ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_ORIGTOTCOST', 'PR_P6TOTCOST', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTNOMSCORE', 'PR_TOTSCORE', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_MODELDATE', 'PR_PARTOOLDATE', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_STRPARDATE'], false), ("PR_SWIM_ONLY='No' AND PR_PART_ONLY='No' AND PR_NONDISTRICT='No' AND (PR_STATUS='01. Submitted – Pending Review/Scoring' OR PR_STATUS='02. Scored' OR PR_STATUS='03. Advanced SW Planning' OR PR_STATUS='04. Design' OR PR_STATUS='05. Construction' OR PR_STATUS='07. Completed')" ))) // ranks to add to the output var rank_fields = [ {name: 'exhydpar_rank', type:'esriFieldTypeInteger'}, {name: 'exstrpar_rank', type:'esriFieldTypeInteger'}, {name: 'extotpar_rank', type:'esriFieldTypeInteger'} ] var out_dict = { fields: Splice(Schema(fs)['fields'], rank_fields), geometryType: '', features: [] } // How is full_count used? I am getting a warning that it is not used var full_count = Count(fs) for (var f in fs) { // convert feature to dict var feat_dict = FromJSON(Text(f)) // calculate ranks feat_dict['attributes']['exhydpar_rank'] = getRank(f, 'PR_EXHYDPAR', fs) feat_dict['attributes']['exstrpar_rank'] = getRank(f, 'PR_EXSTRPAR', fs) feat_dict['attributes']['extotpar_rank'] = getRank(f, 'PR_EXTOTPAR', fs) // convert back to feature, push into output Push(out_dict['features'], Feature(feat_dict)) } return FeatureSet(Text(out_dict))

you!

Re: ArcGIS dashboards Data Expression including Rank

jcarlson — Mon, 23 Sep 2024 21:54:10 GMT

The error is in the getRank function. The SQL statement should be a backtick string, sorry!

`${field} < @filt_val`

To answer your questions:

The entire "Memorize" function is just taking your FeatureSet and pushing it into a new FeatureSet that is held in your browser's memory. That way future requests for information (like the Filter and Count functions) work with local data. Normally, your expression would have to wait for queries to be sent to the feature service each time.

On "GetRank", it doesn't identify the smallest feature. One of the parameters is "feat", and the function can be called on any individual feature. Since we're just defining the function, it's not being used in any particular order.

To explain it again, imagine you have 8 values. If you simply take each value and count the number of features that are smaller than it, that is the rank. You don't actually need to go through each feature counting by ones.

Full count isn't being used, it was an earlier idea I forgot to remove. You can delete it!

Re: ArcGIS dashboards Data Expression including Rank

LJackson29 — Tue, 24 Sep 2024 12:16:51 GMT

@jcarlson Sorry - I changed the backticks to single quotes because I was getting an error that "'filt_val' is assigned but never used." However, when I changed it back to the backticks, it still isn't working. I get the following error when I run the code "Test execution error: Execution error - Portal is required. Verify test data."

Re: ArcGIS dashboards Data Expression including Rank

jcarlson — Tue, 24 Sep 2024 12:51:40 GMT

That's right here:

var fs = Memorize(filter(FeatureSetByPortalItem( portal,'01610f3a8912467c8b06f4b223a6b5e1', 0, ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_ORIGTOTCOST', 'PR_P6TOTCOST', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTNOMSCORE', 'PR_TOTSCORE', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_MODELDATE', 'PR_PARTOOLDATE', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_STRPARDATE'], false), ("PR_SWIM_ONLY='No' AND PR_PART_ONLY='No' AND PR_NONDISTRICT='No' AND (PR_STATUS='01. Submitted – Pending Review/Scoring' OR PR_STATUS='02. Scored' OR PR_STATUS='03. Advanced SW Planning' OR PR_STATUS='04. Design' OR PR_STATUS='05. Construction' OR PR_STATUS='07. Completed')" )))

You're supplying the variable "portal", but it's not defined. It's also the name of a function, which is why it doesn't immediately get flagged as an issue. (In your original expression, it was "port".)

Personally, I don't really find it worth it to establish a separate variable for the portal connection, just use the Portal function directly in your FeatureSet function.

Some other notes:

It looks like you had an extra set of parentheses in your expression. You don't need to put parentheses around your filter statement.

In your filter statement, you've got a lot of "OR" conditions strung together. Since they're all checking the same field, you can use the SQL operator IN to check for any of a series of values.

The expression "some_field = 'A' OR some_field = 'B' OR some_field = 'C'" is equivalent to "some_field IN('A', 'B', 'C')".

Try using this to replace your "var fs = …" section:

var fs = Memorize( Filter( FeatureSetByPortalItem( Portal('https://arcgis.com'), 0, ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_ORIGTOTCOST', 'PR_P6TOTCOST', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTNOMSCORE', 'PR_TOTSCORE', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_MODELDATE', 'PR_PARTOOLDATE', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_STRPARDATE'], false ), "PR_SWIM_ONLY='No' AND PR_PART_ONLY='No' AND PR_NONDISTRICT='No' AND PR_STATUS IN('01. Submitted – Pending Review/Scoring', '02. Scored', '03. Advanced SW Planning', '04. Design', '05. Construction', '07. Completed')" ) )

Re: ArcGIS dashboards Data Expression including Rank

LJackson29 — Tue, 24 Sep 2024 15:06:54 GMT

@jcarlson So sorry, I am still running into issues. I am now getting an error "Cannot read properties of null (reading 'toString'). Verify test data." I updated the code as you suggested (and added the item id). I am not getting any errors in the code, just the warning "'filt_val' is assigned but never used." Console(Count(fs)) - returns the correct number of records.

function Memorize(fs) { var temp_dict = { fields: Schema(fs)['fields'], geometryType: '', features: [] } for (var f in fs) { var attrs = {} for (var attr in f) { attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]) } Push( temp_dict['features'], {attributes: attrs} ) } return FeatureSet(Text(temp_dict)) } function getRank(feat, field, set){ var filt_val = feat[field] var filt_set = Filter(set, `${field} < @filt_val`) return Count(filt_set)+1 } var fs = Memorize( Filter( FeatureSetByPortalItem( Portal('https://arcgis.com'), '01610f3a8912467c8b06f4b223a6b5e1', 0, ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_ORIGTOTCOST', 'PR_P6TOTCOST', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTNOMSCORE', 'PR_TOTSCORE', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_MODELDATE', 'PR_PARTOOLDATE', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_STRPARDATE'], false ), "PR_SWIM_ONLY='No' AND PR_PART_ONLY='No' AND PR_NONDISTRICT='No' AND PR_STATUS IN('01. Submitted – Pending Review/Scoring', '02. Scored', '03. Advanced SW Planning', '04. Design', '05. Construction', '07. Completed')" ) ) Console(Count(fs)); Console(fs); // ranks to add to the output var rank_fields =[ {name: 'exhydpar_rank', type:'esriFieldTypeInteger'}, {name: 'exstrpar_rank', type:'esriFieldTypeInteger'}, {name: 'extotpar_rank', type:'esriFieldTypeInteger'} ] var out_dict = { fields: Splice(Schema(fs)['fields'], rank_fields), geometryType: '', features: [] } for (var f in fs) { // convert feature to dict var feat_dict = FromJSON(Text(f)) // calculate ranks feat_dict['attributes']['exhydpar_rank'] = getRank(f, 'PR_EXHYDPAR', fs) feat_dict['attributes']['exstrpar_rank'] = getRank(f, 'PR_EXSTRPAR', fs) feat_dict['attributes']['extotpar_rank'] = getRank(f, 'PR_EXTOTPAR', fs) // convert back to feature, push into output Push(out_dict['features'], Feature(feat_dict)) } return FeatureSet(Text(out_dict))