Filter for next available day

[agrawaluk]

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!

[BrandonB · Accepted 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. 

[agrawaluk]

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

[BrandonB]

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. 

[agrawaluk]

With the group by function, it totally works. However, I just tried it and DateTimeAdd(DateTimeNow(), 1, "Days") would give no records. I am attaching sample wf. Is there something I was missing?

[BrandonB]

[Settlement Date] > ToDate(DateTimeAdd(DateTimeToday(), 1, "days"))

[BrandonB]

Also make sure that Settlement Date is in an actual date data type

[agrawaluk]

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

[grossal · Accepted solution]

I think Brandon means something like this: 

You first reset your filter to what he originally suggested:

And than use a sample + group by option to always get the first per ID.

I'll attach your modified workflow.

Best

Alex

[agrawaluk]

Ah! Thanks for sharing this. This totally works!