Issues Symbolizing based on Arcade Expression

[JasonCyphers]

I have a spatial view of a valve feature and its related table.  I used the below Arcade expression to symbolize the valves based on the last inspection date:

var DateDifference = DateDiff(Today(), $feature.InspectionDate, "month")
if (DateDifference <= 12){
return 'Within 12 Months'
}
else if(DateDifference <= 14){
return '12-14 Months'
}
else if(DateDifference == 14){
return '14 Months'
}
else {
return 'Over 14 Months'
}

Not all of the valves are being symbolized correctly, however.

The valve point feature is layered underneath the spatial join in the map, and its pop-up uses an arcade expression to show the last inspection date from the related table.  In the screenshow below, you can see that a valve that was inspected 5 months ago is being symbolized as being inspected more than 14 months ago.

Any thoughts?

[jcarlson]

Is it possible that the join layer has multiple records in it for a given point? If your main points layer is where the popups are coming from, you won't get a "1 of x" notice, but check that join layer. If there are more than one points at the same spot, the symbols will show in ascending objectid order, unless otherwise specified, and an older inspection will symbolize on top of a newer one.

[JasonCyphers]

That could be the case.

I tried adding an "OrderBy" clause to the expression, but receive a "Parse Error:orderby is not available" error.

[jcarlson]

I think AGOL recently added a display order setting to the new map viewer, so that you could have symbols' z-index set by an attribute. Maybe that's in the latest Portal, too, but I'm still on 10.9.1, so I don't know for sure.

[JasonCyphers]

Thanks for the feedback.  I'm on Portal 10.8; so, it might be a lost cause.  

[jcarlson]

Well, how are you performing that join? It's possible that process could be amended to filter older records for a given feature.

[JasonCyphers]

The join is being hosted on our SQL server, and is published to Portal.

[jcarlson]

Got it. Well, what about something like a row number?

WITH your_query as (
  SELECT 
    fields, 
    you, 
    want, 
    ROW_NUMBER() OVER(
      PARTITION BY point_id 
      ORDER BY 
        inspection_date DESC
    ) AS rn 
  FROM 
    some_table t1 
    JOIN other_table t2 on t1.id = t2.id
) 
SELECT 
  fields, 
  you, 
  want 
FROM 
  query 
WHERE 
  rn = 1