Arcade calculated expression help

[Lee_Butler]

Hi all,

I hoping to get some help with a bit of Arcade code I'm using to calculate a value for another field.

I have a field, TargetExchange which a user populates with a date dd/mm/yyyy.  I want the arcade expression to caluclate the FinacialYear field based on the date entered.  I have set this up in the layers form but when I test by entering a date in the TargetExchange field I am only recieving a "None" value in the FinancialYear field.  Any ideas where I have gone wrong:

var dates = $feature.TargetExchange
var financialYear = When(
   dates >= 01/04/2023 && dates < 01/04/2024, "2023/2024",
   dates >= 01/04/2024 && dates < 01/04/2025, "2024/2025",
   dates >= 01/04/2025 && dates < 01/04/2026, "2025/2026",
   dates >= 01/04/2026 && dates < 01/04/2027, "2026/2027",
   dates >= 01/04/2027 && dates < 01/04/2028, "2027/2028",
   dates >= 01/04/2028 && dates < 01/04/2029, "2028/2029",
   dates >= 01/04/2029 && dates < 01/04/2030, "2029/2030",
   "None")

return financialYear

Thanks

Lee

[JohannesLindner · Accepted solution]

It doesn't work because you're not comparing to a date. Instead you compare a date (a very large number - milliseconds since January 1, 1970 UTC) to a very small number (1/4/2024 = 0.00012).

Instead, you have to compare to actual dates:

// month is 0 based -> January = 0, April = 3

var financialYear = When(
   dates >= Date(2023, 3, 1) && dates < Date(2024, 3, 1), "2023/2024",
   // ...
   "None")

This is very cumbersome. A better way:

var dates = $feature.TargetExchange
var y = Year(dates)
var m = Month(dates)
return IIf(
    m >= 3,        // if after April
    `${y}/${y+1}`, // use "current/next"
    `${y-1}/${y}`) // else use "previous/current"

[Lee_Butler]

Johannes you are a star! Thank you!  Using your solution has worked perfectly.