Find closest date

[alexbjorlig]

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:

Lightningdate = AddMonths(Startdate, 18)

[Gysbert_Wassenaar]

Maybe like this: Min( If(EventDate>AddMonths(Startdate,18),EventDate))

[rwunderlich]

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://masterssummit.com

http://qlikviewcookbook.com

[Peter_Cammaert]

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.

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[alexbjorlig · Accepted solution]

Thanks for the constructive answers Peter and Rob!

The following gave me the desired result.

if(AddMonths(myDate, 18) > MakeDate(Year(AddMonths(myDate, 18)),9,1), MakeDate(Year(AddMonths(myDate, 18)+1), 3, 1)
,if(AddMonths(myDate, 18) >= MakeDate(Year(AddMonths(myDate, 18)),3,1), MakeDate(Year(AddMonths(myDate, 18)), 9, 1)
,MakeDate(Year(AddMonths(myDate, 18)), 3, 1)
))