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 |
Solved! Go to Solution.
Expected Output should be as per attachment under B column
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.
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
Hope this helps : )
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
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 :)