topic Re: Find closest date in QlikView

Find closest date

alexbjorlig — Wed, 06 Jan 2016 14:58:34 GMT

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)

Re: Find closest date

Gysbert_Wassenaar — Wed, 06 Jan 2016 17:46:04 GMT

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

Re: Find closest date

rwunderlich — Wed, 06 Jan 2016 23:40:59 GMT

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

Re: Find closest date

Peter_Cammaert — Wed, 06 Jan 2016 23:51:56 GMT

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

Peter_Cammaert — Wed, 06 Jan 2016 23:56:18 GMT

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

Re: Find closest date

Peter_Cammaert — Wed, 06 Jan 2016 23:58:56 GMT

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

Re: Find closest date

alexbjorlig — Thu, 21 Jan 2016 10:38:02 GMT

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)
))