Need help with Arcade Data Expression in Dashboard

[M_M]

I am new to Arcade and have found myself stuck.  I am writing a data expression for dashboard and I can't figure out how to write a calculation.  I get an error with everything I've tried.

• I need to multiply the Tally field for records with a 'Plot_Size' of 0.02 by 50, and multiply any records with a 'Plot_Size' of 0.01 by 100. 
• I created a filter to parse out records with 'Plot_Size' 0.02 & 0.01.  I tried to create a new var plot_exp to do the calculation but it's not working (line 10).
• I also tried to do the calculation in my GroupBy (line 20) and it's not recognizing the var I created in the filters on line 6-7.  

I also tried to create a dictionary, and use that but I couldn't get that to work either 🙂

My end goal is to multiply the 'Tally' field for each 'Stand_No' by 50 or 100 (as determined by 'Plot_Sizes' of 0.02 and 0.01).  I then need to find the average for those new (Tally*50 and Tally*100) numbers for each 'Stand_No'.

  var p = 'https://maps.arcgis.com'
  var itemId = 'a8af124e92af44579e3fd07bb59ebb4c'
  var fs = FeatureSetByPortalItem(Portal(p), itemId, 0, ['Stand_No', 'Tally', 'Plot_Configuration', 'Plot_Name', 'Plot_Status', 'Plot_Size', 'DBH', 'Height', 'Cruiser_Name', 'Exit_Plot_Time'], false)
 
  //Separate Plot Size into 2 groups for planting
  var plot50 = Filter(fs, 'Plot_Size = (0.02)');
  var plot100 = Filter(fs, 'Plot_Size = (0.01)');

  var TPS = 0

  //Multiply the plot size in plot50 by 2,500, multiply the plot size in plot100 by 10,000)
  var plot_exp = [{name: 'TPS', expression: '(Tally * 2500)', statistic: 'MAX'}]
  //return plot_exp
  
  var summary = GroupBy(fs,['Stand_No','Plot_Configuration','Plot_Size', 'Cruiser_Name'], 
    [{name: 'TallySum', expression: 'Tally', statistic: 'SUM'},
      {name: 'TCount', expression: '1', statistic: 'COUNT'},
      {name: 'AvgDBH', expression: 'DBH', statistic: 'AVG'},
      {name: 'AvgHeight', expression: 'Height', statistic: 'AVG'},
      {name: '50TPS', expression: '(@plot50 * 2500)', statistic: 'AVG'}])
        Console(summary)

  return summary
 
  //Goal is to obtain the Trees per Acre per Stand for each Stand

[jcarlson · Accepted solution]

The SQL expressions used in GroupBy will recognize numeric and text data, but not objects like FeatureSets. If you need to apply a different calculation based on a field's value, you can do that in the SQL expression using CASE.

var plot_exp = `CASE
    WHEN Plot_Size = 0.02 THEN Tally * 0.02
    WHEN Plot_Size = 0.01 THEN Tally * 0.01
    ELSE Tally -- or some other default value
END`

 Just swap that in as one of the expressions in your GroupBy function, and you should get results like you expect.

[M_M]

That works perfectly, thank you so much!! I really appreciate the help! 🙂