Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My Expression was set to Max[Next Car Repair], however, I am trying to obtain the next car repair date.
For example in the Next Car Repair Column I am trying to obtain 09/25/2022 rather than 09/17/2023. What is the best way to use set expression for this?
Category | Last Car Repair | Next Car Repair |
Car Repair | 5/15/2009 | 4/6/2012 |
Car Repair | 9/9/2009 | - |
Car Repair | 10/23/2014 | 11/30/2019 |
Car Repair | 9/28/2018 | 9/25/2022 |
Car Repair | 4/17/2020 | 9/17/2023 |
This works for the limited amount of sample data you provided:
DATE(MIN({<[Next Car Repair] = {"=RANK(-FABS([Next Car Repair] - TODAY())) = 1"}>} [Next Car Repair]))
It will give you the date closest to today whether it's in the past or future.
Do you mean to get the next one after today? If so, this should work:
DATE(MIN({<[Next Car Repair] = {">=$(=TODAY())"}>} [Next Car Repair]))
However, for the example data in your post, it is still going to return 9/17/2023 because 9/25/2022 is before today.
Hello,
No I am trying to obtain 9/25/2022. But i see the logic that is being used here. What if instead of 09/25/2022 the date was 11/15/2022 as a replacement but it is not the next date?
Category | Last Car Repair | Next Car Repair |
Car Repair | 5/15/2009 | 4/6/2012 |
Car Repair | 9/9/2009 | - |
Car Repair | 10/23/2014 | 11/30/2019 |
Car Repair | 9/28/2018 | 11/15/2022 |
Car Repair | 4/17/2020 | 9/17/2023 |
Actually I have a follow up to the original question... What if I am trying to get 09/25/2022 even if it's technically before today?
If the "next date" isn't the first date after today, how do you determine what the "next date" is? I need to know what logic you're trying to replicate before I can help you with the code.
Understand. The logic I am trying to capture is to get 09/25/2022 from the original data set. I am having trouble capturing the 9/25/2022 since it is the most recent date but it is before today and it is not the MAX date either.
Category | Last Car Repair | Next Car Repair |
Car Repair | 5/15/2009 | 4/6/2012 |
Car Repair | 9/9/2009 | - |
Car Repair | 10/23/2014 | 11/30/2019 |
Car Repair | 9/28/2018 | 9/25/2022 |
Car Repair | 4/17/2020 | 9/17/2023 |
This works for the limited amount of sample data you provided:
DATE(MIN({<[Next Car Repair] = {"=RANK(-FABS([Next Car Repair] - TODAY())) = 1"}>} [Next Car Repair]))
It will give you the date closest to today whether it's in the past or future.