Add 15 days to "Due date" and get values as 1st/2nd/3rd on new field

[Yogish]

I have below date field with multiple dates in it as below. I required to create a new field based on the "Due dates"  as below 

Due date <= 15 days - no intimation

Due date >= 15 days - 1st intimation

Due date >= 30 days - 2nd Intimation

Due date >= 45 days - 3rd Intimation

Due date > =60 days - Over due 

Appreciate quick help on this 

Due Dates

9/1/2022

8/25/2022

8/30/2022

8/15/2022

8/10/2022

8/11/2022

8/12/2022

8/1/2022

8/30/2022

8/12/2022

8/1/2022

7/6/2022

9/1/2022

7/1/2022

7/25/2022

8/21/2022

7/25/2022

[atcodedog05]

Hi @Yogish 

Can you provide us with the expected output and tell us what you have tried so far?

[Yogish]

Expected Output should be as per attachment under B column

[flying008]

Hi,@Yogish 

First, I change your rule1 from 'Due date <= 15 days - no intimation' to 'Due date < 15 days - no intimation', so that way you won't get confused.

Then, use the formula:

Switch(floor(DateTimeDiff(DateTimeToday(),DateTimeParse([Due Dates],"%m/%d/%Y"),"day")/15),"Over due",0,"no intimation",1,"1st intimation",2,"2nd Intimation",3,"3rd Intimation",4,"Over due")

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

[atcodedog05 · Accepted solution]

Hi @Yogish 

Here is how you can build the solution by yourself.

1. Convert input to ISO date by using DateTimeParse(). Because Alteryx can only handle dates in ISO format

2. Calculated days between Today and Due Date by using DateTimeDiff()

refer below page to understand how to use DateTimeParse() and DateTimeDiff()

https://help.alteryx.com/20221/designer/datetime-functions 

3. Bucket it using IF-Else condition block

refer below page to understand how to use IF-Else condition block

https://community.alteryx.com/t5/Interactive-Lessons/Writing-Conditional-Statements/ta-p/98910#done 

You can refer below solution if you get stuck

Spoiler

Hope this helps : )

[flying008]

Hi, @atcodedog05 

Dear, Because the IF judgment conditions have priority, so maybe your step-3 formula can be optimized as follows:

If [Duration from today]>=60  
Then "Over due"
Elseif [Duration from today]>=45
Then "3rd intimation"
Elseif [Duration from today]>=30
Then "2nd intimation"
Elseif [Duration from today]>=15
Then "1st intimation"
Else "no intimation" Endif

[atcodedog05]

Hi @flying008 

I agree with both of your solutions they are both optimized. 

I proposed my solution based on easy to follow and ease of use.

Thank you for your suggestion. Really appreciate it :)

[flying008]

Hi,@atcodedog05 

You are welcome !