Select to view content in your preferred language

Filter not working in arcade expression

206
2
04-22-2024 12:18 PM
SarahW2799
New Contributor

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):

 SarahW2799_0-1713813197099.png

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

SarahW2799_1-1713813298032.png

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

SarahW2799_2-1713813354664.png

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.

0 Kudos
2 Replies
marksm_macomb
Occasional Contributor

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'")

KenBuja
MVP Esteemed Contributor

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'));

 

 

0 Kudos