topic Re: Filter not working in arcade expression in ArcGIS Dashboards Questions

Filter not working in arcade expression

SarahW2799 — Mon, 22 Apr 2024 19:18:59 GMT

Hi there,

Can someone tell me what I'm doing wrong? Still very new to arcade and trying to get a custom dashboard indicator with an expression.

I know I need to return a featureset to get it into a dashboard, I just haven't gotten to that point yet.

First, I need to figure out why my filter is not working. If I put EditDate IS NULL, I get the correct number of features (0):

I just can't figure out how to format to count features where EditDate is equal to 04/22/2024.

I attached a screenshot of the table with the field and attribute I'm trying to filter.

I want to count all the features where the EditDate field is equal to 04/22/2024. This field is a text type. There is no field name alias. There is no domain.

var features_washington = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), 'e5dd4d84533b49e1978abbd23228b6b7'); var total = Count(Filter(features_washington, 'EditDate = 04/22/2024')); return total

Thank you.

Re: Filter not working in arcade expression

marksm_macomb — Mon, 22 Apr 2024 20:18:09 GMT

I think it might just be that you're missing quotes within your SQL statement since that field is text.

In your Filter statement, try Filter(features_washington, "EditDate = '04/22/2024'")

Re: Filter not working in arcade expression

KenBuja — Tue, 23 Apr 2024 15:39:14 GMT

Querying dates in SQL can be picky, especially if there is time involved. In some testing I was doing on my data, if I queried it using "Date = '07/10/2016'", it would return 0 features. If I queried using "Date > '07/10/2016' AND DATE1 <= '07/11/2016'", it would return 29 records. The query "cast(Date1 as date) = cast('7/10/2016' as date)" also returned 29 records. However, there are 36 records on that date, since I have several records after 8 pm ET (which is after midnight on the next day in UTC).

This query finally returned all 36 records

var offset = -TimeZoneOffset(Now()); //returns time zone offset in milliseconds. Use - for Western Hemisphere var startDate = DateAdd(Date(2016,06,10), offset, 'milliseconds'); var endDate = DateAdd(startDate, 1, 'day'); var filtered = Filter(fs, "Date1 > @startDate AND DATE1 <= @endDate"); return (Orderby(filtered, 'Date1 ASC'));