Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
griffin2571
Contributor
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)
1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

6 Replies
Nicole-Smith

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.

griffin2571
Contributor
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
griffin2571
Contributor
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?

Nicole-Smith

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.

griffin2571
Contributor
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
Nicole-Smith

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.