Hello,
I hope someone can help since I am struggling with Date filter in an Arcade expression.
It is about Covid incidence and I need this for my work.
I would like to have an Indicator that shows me if the Incidence of the most recent record (DayZero) has increase or decreas, compared to the day prior of the most recent record (DayOne). I don´t use today and yesterday becasue the incidence is not always daily acutlized.
I am also not sure when I am suppossed to use '' or "" to reference fields (feature field and arcade dictionaries fields) in my expressions.
Let´s suppose my table looks like this:
ObjID | reportdate | incidence |
56 | 03.03.2022 | 1152,4 |
55 | 01.03.2022 | 1428,66 |
54 | 28.02.2022 | 1527,78 |
53 | 27.02.2022 | 1513,9 |
I tried the method shown here(Introducing Data Expressions in ArcGIS Dashboards (esri.com)) but it doesnt work. Any ideas pleas? 🙂
I thank you so much in advance!
Code is here:
var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'),
'xxx' , 0, ['incidence','reportdate'], false)
// Filter the dataset to only have the most recent date.
var day_zero = Date(Max(fs,"reportdate"))
var day_zero_filter = Filter(fs, "reportdate = @day_zero")
// Filter the dataset to only have the day before the most recent date
var day_one = DateAdd(Date(Max(fs, 'reportdate')), -1, 'days')
var day_one_filter = Filter(fs, 'reportdate < @day_zero')
// Dictionary to hold ratio rounded with 2 decimals.
var ratioDict = {
'fields':
[{ 'name': 'ratio',
'type': 'esriFieldTypeDouble'
}],
'geometryType': '',
'features':
[{'attributes':
{'ratio': Round((Max(day_zero_filter ,'incidence') - Max(day_one_filter,'incidence')/Max(day_zero_filter ,'incidence'))*100,2),
}}]};
// Return dictionary as a featureSet.
return FeatureSet(Text(ratioDict));
Solved! Go to Solution.
Hello Everyone,
I post here the soloution/workaround I found. I actually enden up using the Today(), becuase only with this one the = functioned.
var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'),
'xxxxx' , 0, ['incidence','reportdate'], false)
// Filter the dataset to only have the past 2 days.
var day_zero = DateAdd(Today(), -1, 'days')
var day_zero _filter = Filter(fs, "reportdate = @day_zero")
var day_one = DateAdd(Today(), -2, 'days')
var day_one_filter = Filter(fs, 'reportdate = @day_one')
// Dictionary to hold data.
var ratioDict = {
'fields':
[{ 'name': 'ratio',
'type': 'esriFieldTypeDouble'
}],
'geometryType': '',
'features':
[{'attributes':
{'ratio': Round(((Max(day_zero ,'incidence') - Max(day_one,'incidence'))/Max(day_zero ,'incidence'))*100,2),
}}]};
// Return dictionary as a featureSet.
return FeatureSet(Text(ratioDict));
Hard to say without access to the data, but it may be easier to diagnose if we do more of the work outside of the ratioDict. That way you can pass any of your variables to the Console and check their outputs.
Also, don't forget about the order of operations! They way you've written this, the expression will evaluate the division before the subtraction, and won't give you the results you want. But it's hard to see that with all the parentheses in a single line.
Another point: while you can use the "@var" method to pip variables in to your filter statement, I personally prefer using backtick strings, where anything can be piped in inside of a ${ }. This means you don't need to establish temporary variables for a single use, you just use those values right when you need them.
Finally, the indicator itself has formatting options built in. Just worry about getting the number out, then format it as needed in the indicator.
var fs = FeatureSetByPortalItem(
Portal('https://www.arcgis.com'),
'xxx',
0,
['incidence','reportdate'],
false
)
// Filter the dataset to only have the most recent date.
var day_zero_filter = Filter(fs, `reportdate = ${Date(Max(fs,"reportdate"))}`)
// Filter the dataset to only have the day before the most recent date
var day_one_filter = Filter(fs, `reportdate < ${DateAdd(Date(Max(fs, 'reportdate')), -1, 'days')}`)
// Construct the ratio
var zero_incidence = Max(day_zero_filter, 'incidence')
var one_incidence = Max(day_one_filter, 'incidence')
var ratio = (zero_incidence - one_incidence) / zero_incidence
// Dictionary to hold ratio rounded with 2 decimals.
var ratioDict = {
fields: [{name: 'ratio', type: 'esriFieldTypeDouble'}],
geometryType: '',
features: [{attributes: {ratio: ratio}}]
}
// Return dictionary as a featureSet.
return FeatureSet(Text(ratioDict));
Hi Carlson,
thnaks for your prompt response. I just was trying to taste the single variables and found this problem:
when I use the = sign before the @day_filter it gives me an empty result (IMG1). But the if I use > or even < it gives me a correct result (IMG2). It is so frustrating!! I also tried == but went worse ...(IMG3)
IMG 1
IMG2
IMG 3
I will now try out your solution...! I will let you know!
thanks a lot!!
Hey hi!
I guess I am doing something wrong because the script doesn´t work, and if I only try the Return of first sentence (var day_zero_filter) it also gives me an error.
Any ideas...? Cheers!!
Hello Everyone,
I post here the soloution/workaround I found. I actually enden up using the Today(), becuase only with this one the = functioned.
var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'),
'xxxxx' , 0, ['incidence','reportdate'], false)
// Filter the dataset to only have the past 2 days.
var day_zero = DateAdd(Today(), -1, 'days')
var day_zero _filter = Filter(fs, "reportdate = @day_zero")
var day_one = DateAdd(Today(), -2, 'days')
var day_one_filter = Filter(fs, 'reportdate = @day_one')
// Dictionary to hold data.
var ratioDict = {
'fields':
[{ 'name': 'ratio',
'type': 'esriFieldTypeDouble'
}],
'geometryType': '',
'features':
[{'attributes':
{'ratio': Round(((Max(day_zero ,'incidence') - Max(day_one,'incidence'))/Max(day_zero ,'incidence'))*100,2),
}}]};
// Return dictionary as a featureSet.
return FeatureSet(Text(ratioDict));