Calculating Range With Arcade Expressions for Indicators in Dashboards Beta

[Anonymous User]

I'm creating a dashboard to display monthly statistics from weekly pump station inspections.  I need to calculate the range for the field, Run Hours.  Aka I'd like to take the Max value minus the Min Value.  Below is a snippet of the data I'm working with.  I have a category selector that will filter the Table based off a date range and specific pump station.  Without the category selector, the expression Max($datapoint["RunHours"]) returns the overall max value for the field.  However when I apply the category selector, it only spits out the minimum value from the queried range of values.  So it seems  like it's not properly calculating the max value once I apply the category selector. 

Pump Station #	Date	Run Hours
1	1/2/21	718.60
1	1/9/21	(Blank or Null)
1	1/20/21	719.50
2	1/15/21	500.0

After setting the category selector to Pump Station 1 for the month of January, these are my results

Expression	Expected Result	Actual Result
Max($datapoint["RunHours"])	719.5	718.6
Min($datapoint["RunHours"])	718.6	718.6
Max($datapoint["RunHours"]) - Min($datapoint["RunHours"])	0.9	0
Max($datapoint["RunHours"]) + Min($datapoint["RunHours"])	1438.1	1437.2

Below is the first expression I tried resulting in 0.  (see the line starting with "middleText..")

return {
    textColor:'',
    backgroundColor:'',
    topText: 'Generator Run Hours',
    topTextColor: '',
    topTextOutlineColor: '',
    topTextMaxSize: 'medium',
    middleText: Max($datapoint["RunHours"])-Min($datapoint["RunHours"]),
    middleTextColor: '',
    middleTextOutlineColor: '',
    middleTextMaxSize: 'large',
    //bottomText: '',
    //bottomTextColor: '',
    //bottomTextOutlineColor: '',
    //bottomTextMaxSize: 'medium',
    //iconName:'',
    //iconAlign:'left',
    //iconColor:'',
    //iconOutlineColor:'',
    //noValue:false,
    //attributes: {
      // attribute1: '',
      // attribute2: ''
    // }
  }

Below is the second expression I've tried, also resulting in 0. (see the line starting with "middleText..")

var maxhrs = Max($datapoint["RunHours"])
var minhrs = Min($datapoint["RunHours"])

return {
    textColor:'',
    backgroundColor:'',
    topText: 'Generator Run Hours',
    topTextColor: '',
    topTextOutlineColor: '',
    topTextMaxSize: 'medium',
    middleText: maxhrs-minhrs,
    middleTextColor: '',
    middleTextOutlineColor: '',
    middleTextMaxSize: 'large',
    //bottomText: '',
    //bottomTextColor: '',
    //bottomTextOutlineColor: '',
    //bottomTextMaxSize: 'medium',
    //iconName:'',
    //iconAlign:'left',
    //iconColor:'',
    //iconOutlineColor:'',
    //noValue:false,
    //attributes: {
      // attribute1: '',
      // attribute2: ''
    // }
  }

Will someone review my arcade expressions and help me figure out what the issue is? I'm unsure if the Null is messing things up, if it is, how do I tell it to ignore the Nulls?

@XanderBakker  Tagging you because you seemed to help others with related issues.  

[XanderBakker]

Hi @Anonymous User ,

I wish I had an answer for you, but I don't have a lot of experience in using Arcade in the Dashboard. I tried some stuff in the beginning, but it was limited to accessing data in the datapoint only and therefore I haven't used it very much. I have heard that you will be able to use Arcade as data source and that should increment the possibilities to a whole new level (can't wait for that).

What it seems like, is that you just have access to the first datapoint, although filtering for Pump station 1 should get you 3 values. The other thing is how Arcade will treat Null values. have a look at the example below. If a list contains a null value, the value will be treated as 0, in my example, this will return an invalid min statistic and also an invalid average statistic.

[1,2,3,4,5,null]
min:0
max:5
sum:15
avg:2.5

[Anonymous User · Accepted solution]

Thanks for your input!  My coworker was able to figure out a workaround to get the results we need.  

We set the value to the minimum statistic for the "Run Hours" field.

Then we turned on the Reference and set that to the Maximum Statistic for the "Run Hours" field.

Then I references the value and reference value to perform the calculation I needed to get the Range.

var maxhrs = $reference["max_RunHours"]
var minhrs = $datapoint["min_RunHours"]
var rangehrs = maxhrs-minhrs

return {
    textColor:'',
    backgroundColor:'',
    topText: 'Generator Run Hours',
    topTextColor: '',
    topTextOutlineColor: '',
    topTextMaxSize: 'medium',
    middleText: round(rangehrs,1),
    middleTextColor: '',
    middleTextOutlineColor: '',
    middleTextMaxSize: 'large',
    //bottomText: '',
    //bottomTextColor: '',
    //bottomTextOutlineColor: '',
    //bottomTextMaxSize: 'medium',
    //iconName:'',
    //iconAlign:'left',
    //iconColor:'',
    //iconOutlineColor:'',
    //noValue:false,
    //attributes: {
      // attribute1: '',
      // attribute2: ''
    // }
  }