Hi All,
I have a date column as follows:
I am looking to filter date for day after tomorrow (dynamic) but since here 04/09/2020 is not present it should look at next day and if that is also not available then next day and so on. So in this case I should get 04/13/2020.
I am trying to use this formula:
Let me know if this is wrong or if there is any other way to do this. Thank you!
Solved! Go to Solution.
What about a filter tool that says [Settlement Date] > DateTimeAdd(DateTimeNow(), 1, "Days") and then a sample tool afterwords where it picks N number of records with N=1. If the dates are already sorted in order the filter will only pass through the days after tomorrow and then the sample tool will only pass through the next available record.
Hey Brandon,
Thanks for your response. This would work, but I missed a major detail to add to my problem, there is another column "ID" which has the same dates. So in this case sample wouldn't work exactly.
Sample data of what I am looking at
ID | Date |
1 | 4/7/2020 |
1 | 4/8/2020 |
1 | 4/13/2020 |
1 | 4/14/2020 |
1 | 4/15/2020 |
2 | 4/7/2020 |
2 | 4/8/2020 |
2 | 4/13/2020 |
2 | 4/14/2020 |
2 | 4/15/2020 |
What I want the filter to do in this case:
1 | 4/13/2020 |
2 | 4/13/2020 |
The good news is that you can actually accomplish this using the same methodology that I suggested above but with one more adjustment. In the Sample tool you can "Group By" a field. In this case you would group by your ID field by checking the box in the Sample tool and it would pull the first date (assuming they are sorted) from every group of ID's.
[Settlement Date] > ToDate(DateTimeAdd(DateTimeToday(), 1, "days"))
Also make sure that Settlement Date is in an actual date data type
Hey @BrandonB
I tried that as well. But I don't follow how the sample tool would be used. Can you please help me with the workflow? You may use the workflow from my previous response. Really appreciate your time. Thanks
Ah! Thanks for sharing this. This totally works!