Data expression loop through table and create 0 value

ChrisRoberts2 — Mon, 05 Jun 2023 23:24:04 GMT

Hi All

I have a point layer showing deposited lots for each financial year quarter that has been intersect with 3 different levels of monitoring areas (Region, Monitoring Area, Estate). This data is then displayed in a bar graph using the DepositedQuarter field as the category and the sum of the DepositedLots field as the value. I then have a series of selectors to filter down the data based on the areas.

The issue is that there are some areas that dont have any records for a quarter so the quarter disappears and the graph compresses. What I need to do is somehow create a data expression that loops though the table and if a value doesnt exist for any quarter in an area, create one an give it a 0 value in the depositedlots. That way the full category will always display the user can see a better reflection of the trend in the graph.

I have made a start on creating a data expression to get a flat summary table, but I'm not all that experience with data expressions, so I would really appreciate any help or guidance.

var fs = FeatureSetByPortalItem(Portal('https://xxx.maps.arcgis.com'), 'xxxxxxxxxx', 0, ["*"], false);

var fs_gp = GroupBy(fs, ['DepositedQuarter','Region','Key_Area','EstateName'], [{name: 'deposits_by_quarter', expression: 'DEPOSITED_LOTS', statistic: 'SUM'}]);

// Create array for holding features, feat object for populating array

var features = [];

var feat;

// Populate feature array

for (var feature in fs_gp) {

feat = {

'attributes': {

'DepositedQuarter': feature['DepositedQuarter'],

'Region': feature['Region'],

'KeyArea': feature['Key_Area'],

'EstateName': feature['EstateName'],

'Deposits': feature['deposits_by_quarter']

}}

Push(features, feat);

};

var dowDict = {

'fields': [{ 'name': 'DepositedQuarter', 'type': 'esriFieldTypeString'},

{'name': 'Region', 'type': 'esriFieldTypeString'},

{'name': 'KeyArea', 'type': 'esriFieldTypeString'},

{'name': 'EstateName', 'type': 'esriFieldTypeString'},

{'name': 'Deposits', 'type': 'esriFieldTypeInteger'}],

'geometryType': '',

'features': features

};

// Convert dictionary to feature set.

var fs_dict = FeatureSet(Text(dowDict));

return GroupBy(fs_dict, ['Region', 'KeyArea','EstateName','DepositedQuarter'], [{ name: 'deposits_by_quarter', expression: 'Deposits', statistic: 'SUM'}]);

Cheers, Chris

topic Data expression loop through table and create 0 value in ArcGIS Dashboards Questions

Data expression loop through table and create 0 value