Pivots in dashboards using arcade

Manager_HMWSSBCDC

Hi,

 

I have a table having field names as "can", "date" where "can" contains customer unique code and "date" contains product ordered date. Now I want to show count of orders each "can" per each year. How to get achieve a solution for this.

 

 

The picture contains sample I have done using pandas. can i get same output in arcgis dashboards.

jcarlson

I think with a Data Expression this would be simple enough using a GroupBy statement. The only trouble is you'd need to manually adjust the expression for every year you wanted in the output. To get the entire thing to be dynamic and add columns for each year, it would be a bit more involved. But still possible!

Here's the easy way:

 

var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  'itemid of your service',
  0, // layer index
  ['can', 'date'],
  false
)

return GroupBy(
  fs
  'can',
  [
    { name: '2023', expression: 'CASE WHEN EXTRACT YEAR FROM date = 2023 THEN 1 ELSE 0 END', statistic: 'SUM' },
    { name: '2024', expression: 'CASE WHEN EXTRACT YEAR FROM date = 2024 THEN 1 ELSE 0 END', statistic: 'SUM' }
  ]
)