Closest Date and Latest Date from option

Question

Thanks in advance for any help.

I have a list of companies and a list of activities with dates.   Trying to find two different dates.   The date closest to the current date,  and the next following date.

Based on this table,  use 1/31/24  as the current date.

I have used Company Ascending, Date Descending and sampled for latest 1,  but not sure how to get the closest.

Any ideas?

Activity Table.PNG

DataNath · Answer

@DodgerFH here's something I put together. The workflow operates as such:

1) Calculate absolute and non-absolute days diff between today's date and each date

2) The top stream sorts by absolute date ascending and samples the first record by Company to get the closest date

3) The bottom stream filters days diff to be above 0 (ensuring it's in future) and then sorts ascending before sampling the first one to get the next date

4) After that, we conduct a Join in order bring the two dates together for each company

5) We Union the Join streams back together to account for things like a Company not having a 'next' date i.e. in your data, there's nothing planned for Company B

Hope this helps!

Find closest and next date.yxmd

DodgerFH · Answer

I am wanting two dates.  The closest date to current date (Today) and the Max Date.    If today is Jan 31, For Company A  I would like a column returning 1/25/24  and then another separate column showing 2/7/24.    Basically wanting to find the last time a client was met with, AND when the next meeting with the client will be.   If they are the same date,  that is fine.   Next row would be Company B with 1/25/24 in both columns.

Adding sample data set

CompanyEvent Example.xlsx

apathetichell · Answer

assuming your dates are dates - reread. post some data.