topic Re: Find nearest monday from the given date in QlikView

Find nearest monday from the given date

praveenkumar_s — Wed, 25 Nov 2020 16:16:04 GMT

Hi friends,

I want to get the next monday date from the given date like if i have a date called 8/12/2017 . from this date i want to get the next monday date like 11/12/2017 . in the same manner i have to get month last date also like 29/12/2017, i have to get 1/2/2018 in the load script.

Any help is much appreciated friends.

please let me know in case of any clarifications.

Re: Find nearest monday from the given date

swuehl — Tue, 05 Dec 2017 10:01:18 GMT

Maybe something like

Weekstart(Max(DateField)+6)

to get next monday for a given date field value (used Max() in case there are more than one possible values).

Re: Find nearest monday from the given date

tamilarasu — Tue, 05 Dec 2017 10:03:01 GMT

Hi Praveen,

Try,

Date(WeekEnd(Date#('8/12/2017','DD/MM/YYYY'))+1)

Re: Find nearest monday from the given date

Colin-Albert — Tue, 05 Dec 2017 10:07:50 GMT

This will return today's date if today is a Monday, otherwise the next Monday.

if(num(weekday(<YourDateField>)) = 0,

<YourDateField>,

date(<YourDateField>+ 7 - num(weekday(<YourDateField>)) )

)

This will return the next Monday

date(<YourDateField> + 7 - num(weekday(<YourDateField>)) )

Re: Find nearest monday from the given date

prieper — Tue, 05 Dec 2017 10:13:15 GMT

give it a try with

WEEKSTART(MyDate) +7

second one is not quite clear

MONTHSTART(ADDMONTHS(MyDate), 1)) should deliver the first day of the next month

Re: Find nearest monday from the given date

praveenkumar_s — Tue, 05 Dec 2017 10:17:35 GMT

Very thanks, this is exactly what i want.

Re: Find nearest monday from the given date

tamilarasu — Tue, 05 Dec 2017 10:25:26 GMT

Or simply try,

WeekEnd(DateField,0,1)