Announcements
cancel
Showing results for
Did you mean:
Contributor

How to use Set Analysis To Obtain the Next Car Repair Date Without Getting it's Maximum Date

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
Labels (6)

• Set Analysis

1 Solution

Accepted Solutions
MVP

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.

6 Replies
MVP

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.

Contributor
Author

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
Contributor
Author

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?

MVP

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.

Contributor
Author

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
MVP

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.