Arcade calculated expression help

544
2
Jump to solution
02-03-2023 03:18 AM
Labels (1)
LeeButler1
New Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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"

Have a great day!
Johannes

View solution in original post

2 Replies
JohannesLindner
MVP Frequent Contributor

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"

Have a great day!
Johannes
LeeButler1
New Contributor II

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

0 Kudos