Filter a chart using an expression by the date-range picker

192
1
05-08-2024 01:37 PM
Labels (1)
GregSpiridonov
New Contributor III

I am attempting to create a Chart using an expression to parse Survey123 data. The raw data is a string that is comma separated, so it is split and grouped by value to get the number of sources per value.
eg:

"red,blue,green"
"red,green"

to

red : 2
blue: 1
green: 2

The original data also has a survey date. I am using a date picker in the dashboard to filter charts by the date range selected, but since the expression doesn't include a date I can't filter by it.

I've attempted to add the date field to the expressions output, but it only seems to show up if I add the date to the groupby section of my arcade script. The issue there is that the chart then displays a column for each value/date combination

eg:
red/Date1: 1
red/Date2: 1

Besides that issue, the picker seems to work. Any ideas on how to get this to work? My first thought was to somehow get the datepicker range as an input to the expression and to filter the values created by the range before output. Is that possible somehow?

I've included the script below: (edited to remove private data)

 

// Reference layer using the FeatureSetByPortalItem() method.
var p = Portal('https://XXXXX.maps.arcgis.com')
var fs = FeatureSetByPortalItem(
p,
'XXXXUIDXXXX',
0,
['Color', 'SurveyDate'],
false
);

// Create empty array for features and feat object
var features = [];
var feat;

// Split comma separated colors and store in dictionary.  
for (var f in fs) { 
    var split_array  =  Sort(Split(f["Color"], ','))

    
    
    var count_arr = Count(split_array) 
    for(var i = 0; i < count_arr; i++ ){ 
        feat = {
            'attributes': {
                'SurveyDate': f["SurveyDate"],
                'split_choices': Proper(Replace(Trim(split_array[i]), '_',' '))
            }
        }
        Push(features, feat);
}}

// Empty dictionary to capture each color reported as separate rows. 
var choicesDict = {
    'fields': [
        { 'name': 'split_choices', 'type': 'esriFieldTypeString'},
      ], 
    'geometryType': '',
    'features': features
}; 

// Convert dictionary to featureSet. 
var fs_dict = FeatureSet(choicesDict); 

// Return featureset after grouping by color. 
return GroupBy(fs_dict, ['split_choices', 'SurveyDate'], 
       [{ name: 'split_count', expression: 'split_choices', statistic: 'COUNT' }]);  // Write an expression that returns a FeatureSet.

 

  

0 Kudos
1 Reply
jcarlson
MVP Esteemed Contributor

Remember that your chart can do the grouping / counting for you. You don't actually need to use GroupBy here.

But the real root of the problem is your choicesDict. You need to include SurveyDate in the fields list. Right now you only have split_choices in your fields, so there is literally no SurveyDate field present in the resulting FeatureSet, regardless of having that attribute present in each of your features. Just add this line to the fields list if choicesDict:

{ 'name': 'SurveyDate', 'type': 'esriFiedlTypeDate'}
- Josh Carlson
Kendall County GIS
0 Kudos