Hi, I am trying to create a new field named [Values] using the formula tool from [Date] and [Max_Date] field.
I would like one case statement that will result in values 1, 2,3,4,5
IF [Date]>= DateTimeAdd([Max_Date],-14,'days')) and [Date]<= [Max_Date] then 1
ELSEIF [Date]>= DateTimeAdd([Max_Date],-30,'days') and [Date]<= [Max_Date] then 2
ELSEIF [Date]>= DateTimeAdd([Max_Date],-90,'days') and [Date]<= [Max_Date] then 3
ELSEIF [Date]>= DateTimeAdd([Max_Date],-120,'days') and [Date]<= [Max_Date] then 4
ELSE 5
ENDIF
Unfortunately there is an overlap between 1,2,3,4,5 and I am not sure how to do this. Any help is appreciated
Solved! Go to Solution.
@gottaask08 Your formula seems to be working fine. Are you seeing any issues in your output?
IF [Date]>= DateTimeAdd([Max_Date],-14,'days') and [Date]<= [Max_Date] then 1
ELSEIF [Date]>= DateTimeAdd([Max_Date],-30,'days') and [Date]<= [Max_Date] then 2
ELSEIF [Date]>= DateTimeAdd([Max_Date],-90,'days') and [Date]<= [Max_Date] then 3
ELSEIF [Date]>= DateTimeAdd([Max_Date],-120,'days') and [Date]<= [Max_Date] then 4
ELSE 5
ENDIF
@gottaask08 I dont think you need the second part of your if statement, but in any case the formula seemed to be working.
Is your data type a "date" data type?
See attached
The formula is working fine but I think I did not ask my question well. The intention is to have the values field show if a date falls in past 14, past 30, past 90, past 120 or 120 or more days.
That is to say if a date is between 2022-10-12 and 2022-10-26 (the last 14 days) be value =1
if a date is between 2022-09-26 and 2022-10-26 (the last 30 days) be value = 2
etc
Any help is appreciated
The formula in the workflow I attached seems to match your scenario... sorry if im missing something.
Not quite. 2022-10-12 falls in both the past 14 days and the past 30 days. Not sure if I am overthinking this but any help is much appreciated :)
So in that scenario just to be clear, what number would you expect assigned?
hi @gottaask08
Just use select tool/date time tool and change the data type to date.
Then perform your function, your output will be fine.
Hope it helps!!!