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.