6 Replies Latest reply: Jan 21, 2016 5:40 AM by Alex Bjølig

# Find closest date

Ok I have this frustrating task I hope you can help solve:

Known informatoin:

Startdate (presented by red star)

Enddate (presented by the lightning) --> its the startdate + 18 months

Event dates: 1 march and 1 september, every year.

Given any startdate, how do I find the closest future event date?

I created a small picture for visualization:

What I have tried so far:

• ###### Re: Find closest date

If the event dates are fixed at March 1 and Sept 1, it seems that finding the next future event from any date is:

if(myDate > MakeDate(Year(myDate),9,1), MakeDate(year(myDate)+1, 3, 1)

,if(myDate > MakeDate(Year(myDate),3,1), MakeDate(year(myDate), 9, 1)

,MakeDate(year(myDate), 3, 1)

))

-Rob

http://qlikviewcookbook.com

• ###### Re: Find closest date

Does the closest future event date have to be completely outside of the period between StartDate and EndDate? Your drawing seems to imply as much.

• ###### Re: Find closest date

If so, replace all occurrences of myDate in Rob's formula with the expression you made yourself: AddMonths(StartDate, 18)

• ###### Re: Find closest date

If you have a list of EventDates somewhere in your data model, Gysbert's solution will do wonders.

• ###### Re: Find closest date

Thanks for the constructive answers Peter and Rob!

The following gave me the desired result.