topic Re: Generate Week Ending date for each month in App Development

Generate Week Ending date for each month

punitpopli — Mon, 30 Aug 2021 10:56:04 GMT

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

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

Re: Generate Week Ending date for each month

Digvijay_Singh — Mon, 30 Aug 2021 11:49:21 GMT

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,

Re: Generate Week Ending date for each month

punitpopli — Mon, 30 Aug 2021 12:03:09 GMT

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

Re: Generate Week Ending date for each month

Digvijay_Singh — Mon, 30 Aug 2021 12:09:09 GMT

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/DateAndTimeFunctions/weekend.htm

Re: Generate Week Ending date for each month

tresB — Mon, 30 Aug 2021 12:27:03 GMT

@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.

Re: Generate Week Ending date for each month

Or — Mon, 30 Aug 2021 13:30:06 GMT

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.

Re: Generate Week Ending date for each month

punitpopli — Tue, 31 Aug 2021 08:17:55 GMT

Hi @tresB

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!

Re: Generate Week Ending date for each month

punitpopli — Tue, 31 Aug 2021 08:30:10 GMT

Thank you @Or for your reply.

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

Once again, thank you for all your help and solution