Generate Week Ending date for each month

[punitpopli]

Hi ,

I am using below script to generate week Ending date for each month, but the output is not as expected, meaning all the week ending are generated for all the month.

Unable to get what am doing wrong in the code below - 

vNoOfMonths = num(Month(today()))-1;

LET vMinDate = Date(Date#(MonthEnd(Today(),-$(vNoOfMonths)),'MM/DD/YYYY'),'YYYY-MM-DD') ;
LET vMaxDate = Date(Date#(MonthEnd(Today()),'MM/DD/YYYY'),'YYYY-MM-DD');
LET vMonth = ((year(Date(vMaxDate,'DD-MM-YYYY'))*12)+month(Date(vMaxDate,'DD-MM-YYYY'))) - ((year(Date(vMinDate,'DD-MM-YYYY'))*12)+month(Date(vMinDate,'DD-MM-YYYY')));

for a = 0 to vMonth

LET vDate = Date(Date#(MonthEnd(vMinDate,a),'MM/DD/YYYY'),'YYYY-MM-DD');
vWeek = num(MonthStart('$(vDate)'))
vDays = Ceil(MonthEnd('$(vDate)') - MonthStart('$(vDate)'));

For i=1 to $(vDays)+7

vWeekEnding = WeekEnd($(vWeek),0,0);
vWeek =vWeek+7;

Concatenate (WeekEndingTemp)
Load * INline [
WeekEnding, Date
$(vWeekEnding), $(vDate)
];
next

next

The outer loop (with a as variable)  is to run the code under for for every month and for every month have to generate generate the week ending dates 

Any help on this will be really appreciated 

thanks in advance 

[Digvijay_Singh]

Not sure but what if you run below native week end function for each date in your range?

WeekEnd(date [, period_no[, first_week_day]])

Thanks,

[punitpopli]

am getting only the month end date and using above mentioned function will return only the last week end date and not all the Week End dates of the month

[Digvijay_Singh]

May be I am missing something but this Weekend function's job is to return the last day of the week containing date. You have to feed it each day of the month though and then later use distinct to remove duplicates because all 7 days of a week will return the same weekend date.

This is what documentation says and I have used it as well - 

This function returns a value corresponding to a timestamp of the last millisecond of the last day (Sunday) of the calendar week containing date.

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

[tresesco · Accepted solution]

@punitpopli , it should be a bit simpler. May be you get an idea from the given sample snippet. 

Load
        WeekEnd(Date) as WeekEnd,
        Date,
        Month(Date) as Month;
Load
        Date(Today()-90+RecNo()) as Date
AutoGenerate 90;

Then you might want/have to put conditions for inclusion/exclusion of boundary values.

[Or]

If I read the requirement correctly, this should be doable in a single, relatively simple loop once you've generated the dates to be tested (in my case I used an inline load):

Dates:
Load date(DayDate) as DayDate INLINE [
DayDate, Month
01/08/21, 8
02/08/21, 8
03/08/21, 8
04/08/21, 8
05/08/21, 8
06/08/21, 8
07/08/21, 8
08/08/21, 8
09/08/21, 8
10/08/21, 8
];

For each vDayDate in FieldValueList('DayDate')

Weekends:
Load date('$(vDayDate)') as DayDate, WeekEnd('$(vDayDate)') as WeekendDate

Autogenerate(1);

Next;

Note that the week end date may not be within the same month being tested, e.g. if Tuesday, August 31 is being tested for August, the WeekEnd() date will be in September.

[punitpopli]

Hi @tresesco 

Thank you for your reply. This will help alternatively to what I was looking for

I was actually looking for trying to find the Week end date by adding a look on month end date which will not be ideal way to achieve it.

Your solution will work for now. Thank you for all your help!

[punitpopli]

Thank you @Or  for your reply.

This will need me to maintain an inline load with some hardcoding and hence accepting what @tresesco  had suggested.

Once again, thank you for all your help and solution