Select to view content in your preferred language

Arcade Data Expression for Charts

459
5
Jump to solution
08-05-2024 11:59 AM
Labels (1)
jca_geo
Occasional Contributor

Hello everyone! I am new to Arcade and hoping someone would be able to help out with this.

I am trying to create a data expression to be used as a data source for a serial chart.

The following appears to work:

var portalsite = Portal('https://arcgis.com');

var fs = FeatureSetByPortalItem(
    portalsite,
    '6ad1b5d6decd4914a3891953762d241d',
    0,
    [
        'REPORTED_MAGNITUDE',
        'MAGNITUDE_TYPE'
    ],
    false
);

return GroupBy( 
    fs,
    ['REPORTED_MAGNITUDE', 
     'MAGNITUDE_TYPE'],
    [{name:'Total', expression: '1', statistic:'COUNT'}]
);

 

However, aside from REPORTED_MAGNITUDE and MAGNITUDE TYPE, I would also like to return a field that uses the following logic:

When( REPORTED_MAGNITUDE < '3.5', 'Less than 3.5'
       REPORTED_MAGNITUDE >= '3.5' && REPORTED_MAGNITUDE <= '5.4', '3.5 to 5.4'
       REPORTED_MAGNITUDE >= '5.5' && REPORTED_MAGNITUDE <= '6.0', '5.5 to 6.0'
       REPORTED_MAGNITUDE >= '6.1' && REPORTED_MAGNITUDE <= '6.9', '6.1 to 6.9'
       REPORTED_MAGNITUDE >= '7.0' && REPORTED_MAGNITUDE <= '7.9', '7.0 to 7.9'
       REPORTED_MAGNITUDE >= '8.0' && REPORTED_MAGNITUDE <= '8.9', '8.0 to 8.9'
       REPORTED_MAGNITUDE > '8.9', '9.0 and above'

 

I think this is like adding a calculated field. I'm just not sure how to do so in Arcade. Thank you!

0 Kudos
2 Solutions

Accepted Solutions
KenBuja
MVP Esteemed Contributor

Here's one way to do it.

Note that you can simply the When statement, since it evaluates each line sequentially. This means that if a value doesn't meet the first criteria (< 3.5), it moves to the second criteria between 3.5 and 5.4). Since it already has checked if it's below 3.5, it doesn't need to check that again, so it just need to check if it's below 5.5.

 

 

var portalsite = Portal('https://arcgis.com');

var fs = FeatureSetByPortalItem(
    portalsite,
    '6ad1b5d6decd4914a3891953762d241d',
    0,
    [
        'REPORTED_MAGNITUDE',
        'MAGNITUDE_TYPE'
    ],
    false
);

var GroupedFS = GroupBy( 
    fs,
    ['REPORTED_MAGNITUDE', 
     'MAGNITUDE_TYPE'],
    [{name:'Total', expression: '1', statistic:'COUNT'}]
);

var s = Schema(GroupedFS)
Push(s.fields, {'name':'Range', 'alias': 'Magnitude Range', 'type': 'esriFieldTypeString'})

var temp_dict = {
  fields: s['fields'],
  geometryType: '',
  features: []
}

for (var f in fs) {
  var attrs = {}
  for (var attr in f) {
    attrs[attr] = f[attr]
  }
  attrs['Range'] = When( attrs.REPORTED_MAGNITUDE < 3.5, 'Less than 3.5',
       attrs.REPORTED_MAGNITUDE < 5.5, '3.5 to 5.4',
       attrs.REPORTED_MAGNITUDE <= 6.0, '5.5 to 6.0',
       attrs.REPORTED_MAGNITUDE < 7, '6.1 to 6.9',
       attrs.REPORTED_MAGNITUDE < 8, '7.0 to 7.9',
       attrs.REPORTED_MAGNITUDE < 9, '8.0 to 8.9',
       '9.0 and above')
  
  Push(
    temp_dict['features'],
    {attributes: attrs}
  )
}
return FeatureSet(temp_dict)

 

View solution in original post

jcarlson
MVP Esteemed Contributor

You could easily throw it into your GroupBy function. Mind that using greater/less operators require the value to be a number, not a string, so don't put quotes around the numbers.

Also, when we use the When function, or a CASE statement in SQL, the conditions are evaluated in order. We already know that anything being evaluated at the second step (< 5.5) has already failed the preceding condition of being < 3.5, so there's no need to repeat the condition.

var mag_range_sql = `CASE
WHEN REPORTED_MAGNITUDE < 3.5 THEN 'Less than 3.5'
WHEN REPORTED_MAGNITUDE < 5.5 THEN '3.5 to 5.4'
WHEN REPORTED_MAGNITUDE < 6.1 THEN '5.5 to 6.0'
WHEN REPORTED_MAGNITUDE < 7.0 THEN '6.1 to 6.9'
WHEN REPORTED_MAGNITUDE < 8.0 THEN '7.0 to 7.9'
WHEN REPORTED_MAGNITUDE < 9.0 THEN '8.0 to 8.9'
WHEN REPORTED_MAGNITUDE >= 9.0 THEN '9.0 and above'
ELSE 'No Value'
END`

return GroupBy( 
  fs,
  [
    {name: 'REPORTED_MAGNITUDE', expression: 'REPORTED_MAGNITUDE'},
    {name: 'MAGNITUDE_TYPE', expression: 'MAGNITUDE_TYPE'},
    {name: 'magnitude_range', expression: mag_range_sql}
  ],
  [
    {name: 'Total', expression: '1', statistic: 'COUNT'}
  ]
);

 

It looks like you're trying to make a histogram? This is possible in Arcade, but be aware that sorting your bins may not go how you intend. Also, if any of the bins are empty, you're not going to get an empty bin, that category will just be gone.

I wrote a bit about trying to make a custom histogram function over here:

https://www.jcarlson.page/posts/dashboards-histogram/

- Josh Carlson
Kendall County GIS

View solution in original post

5 Replies
KenBuja
MVP Esteemed Contributor

Here's one way to do it.

Note that you can simply the When statement, since it evaluates each line sequentially. This means that if a value doesn't meet the first criteria (< 3.5), it moves to the second criteria between 3.5 and 5.4). Since it already has checked if it's below 3.5, it doesn't need to check that again, so it just need to check if it's below 5.5.

 

 

var portalsite = Portal('https://arcgis.com');

var fs = FeatureSetByPortalItem(
    portalsite,
    '6ad1b5d6decd4914a3891953762d241d',
    0,
    [
        'REPORTED_MAGNITUDE',
        'MAGNITUDE_TYPE'
    ],
    false
);

var GroupedFS = GroupBy( 
    fs,
    ['REPORTED_MAGNITUDE', 
     'MAGNITUDE_TYPE'],
    [{name:'Total', expression: '1', statistic:'COUNT'}]
);

var s = Schema(GroupedFS)
Push(s.fields, {'name':'Range', 'alias': 'Magnitude Range', 'type': 'esriFieldTypeString'})

var temp_dict = {
  fields: s['fields'],
  geometryType: '',
  features: []
}

for (var f in fs) {
  var attrs = {}
  for (var attr in f) {
    attrs[attr] = f[attr]
  }
  attrs['Range'] = When( attrs.REPORTED_MAGNITUDE < 3.5, 'Less than 3.5',
       attrs.REPORTED_MAGNITUDE < 5.5, '3.5 to 5.4',
       attrs.REPORTED_MAGNITUDE <= 6.0, '5.5 to 6.0',
       attrs.REPORTED_MAGNITUDE < 7, '6.1 to 6.9',
       attrs.REPORTED_MAGNITUDE < 8, '7.0 to 7.9',
       attrs.REPORTED_MAGNITUDE < 9, '8.0 to 8.9',
       '9.0 and above')
  
  Push(
    temp_dict['features'],
    {attributes: attrs}
  )
}
return FeatureSet(temp_dict)

 

jca_geo
Occasional Contributor

Great script! Thanks @KenBuja

0 Kudos
jcarlson
MVP Esteemed Contributor

You could easily throw it into your GroupBy function. Mind that using greater/less operators require the value to be a number, not a string, so don't put quotes around the numbers.

Also, when we use the When function, or a CASE statement in SQL, the conditions are evaluated in order. We already know that anything being evaluated at the second step (< 5.5) has already failed the preceding condition of being < 3.5, so there's no need to repeat the condition.

var mag_range_sql = `CASE
WHEN REPORTED_MAGNITUDE < 3.5 THEN 'Less than 3.5'
WHEN REPORTED_MAGNITUDE < 5.5 THEN '3.5 to 5.4'
WHEN REPORTED_MAGNITUDE < 6.1 THEN '5.5 to 6.0'
WHEN REPORTED_MAGNITUDE < 7.0 THEN '6.1 to 6.9'
WHEN REPORTED_MAGNITUDE < 8.0 THEN '7.0 to 7.9'
WHEN REPORTED_MAGNITUDE < 9.0 THEN '8.0 to 8.9'
WHEN REPORTED_MAGNITUDE >= 9.0 THEN '9.0 and above'
ELSE 'No Value'
END`

return GroupBy( 
  fs,
  [
    {name: 'REPORTED_MAGNITUDE', expression: 'REPORTED_MAGNITUDE'},
    {name: 'MAGNITUDE_TYPE', expression: 'MAGNITUDE_TYPE'},
    {name: 'magnitude_range', expression: mag_range_sql}
  ],
  [
    {name: 'Total', expression: '1', statistic: 'COUNT'}
  ]
);

 

It looks like you're trying to make a histogram? This is possible in Arcade, but be aware that sorting your bins may not go how you intend. Also, if any of the bins are empty, you're not going to get an empty bin, that category will just be gone.

I wrote a bit about trying to make a custom histogram function over here:

https://www.jcarlson.page/posts/dashboards-histogram/

- Josh Carlson
Kendall County GIS
KenBuja
MVP Esteemed Contributor

Nice solution. I always forget about utilizing the power of SQL in the GroupBy

0 Kudos
jca_geo
Occasional Contributor

This works great! Thanks for sharing the post as well @jcarlson 

0 Kudos