topic Create Master Calendar period from first Monday of the month until end week of th month in QlikView

Create Master Calendar period from first Monday of the month until end week of th month

dennysetiawan — Sat, 16 Nov 2024 18:05:20 GMT

Hi Expert,

I want to create a master calendar that contains:

*First day = first Monday of every month

*Last day = last weekend of every month

For example,

in Period July-2020, the first day should be 06-Jul-2020 and the last day should be 02-Aug-2020.

in Period Aug-2020, the first day should be 03-Aug-2020 and the last day should be 06-Sep-2020.

in Period Sep-2020, the first day should be 07-Sep-2020 and the last day should be 04-Oct-2020. etc.

Can you have any solution about this case?

Re: Create Master Calendar period from first Monday of the month until end week of th month

tresB — Mon, 21 Sep 2020 05:57:09 GMT

@dennysetiawan So you want only two days in every month?

Re: Create Master Calendar period from first Monday of the month until end week of th month

dennysetiawan — Mon, 21 Sep 2020 06:27:36 GMT

Hi @tresB , not only two days, but all days in every month with condition that I described before.

Period	Date Report	Day Report
07.2020	6-Jul-20	Mon
07.2020	7-Jul-20	Tue
07.2020	8-Jul-20	Wed
07.2020	9-Jul-20	Thu
07.2020	10-Jul-20	Fri
07.2020	11-Jul-20	Sat
07.2020	12-Jul-20	Sun
07.2020	13-Jul-20	Mon
07.2020	14-Jul-20	Tue
07.2020	15-Jul-20	Wed
07.2020	16-Jul-20	Thu
07.2020	17-Jul-20	Fri
07.2020	18-Jul-20	Sat
07.2020	19-Jul-20	Sun
07.2020	20-Jul-20	Mon
07.2020	21-Jul-20	Tue
07.2020	22-Jul-20	Wed
07.2020	23-Jul-20	Thu
07.2020	24-Jul-20	Fri
07.2020	25-Jul-20	Sat
07.2020	26-Jul-20	Sun
07.2020	27-Jul-20	Mon
07.2020	28-Jul-20	Tue
07.2020	29-Jul-20	Wed
07.2020	30-Jul-20	Thu
07.2020	31-Jul-20	Fri
07.2020	1-Aug-20	Sat
07.2020	2-Aug-20	Sun
08.2020	3-Aug-20	Mon
08.2020	4-Aug-20	Tue
08.2020	5-Aug-20	Wed
08.2020	6-Aug-20	Thu
08.2020	7-Aug-20	Fri
08.2020	8-Aug-20	Sat
08.2020	9-Aug-20	Sun
08.2020	10-Aug-20	Mon
08.2020	11-Aug-20	Tue
08.2020	12-Aug-20	Wed
08.2020	13-Aug-20	Thu
08.2020	14-Aug-20	Fri
08.2020	15-Aug-20	Sat
08.2020	16-Aug-20	Sun
08.2020	17-Aug-20	Mon
08.2020	18-Aug-20	Tue
08.2020	19-Aug-20	Wed
08.2020	20-Aug-20	Thu
08.2020	21-Aug-20	Fri
08.2020	22-Aug-20	Sat
08.2020	23-Aug-20	Sun
08.2020	24-Aug-20	Mon
08.2020	25-Aug-20	Tue
08.2020	26-Aug-20	Wed
08.2020	27-Aug-20	Thu
08.2020	28-Aug-20	Fri
08.2020	29-Aug-20	Sat
08.2020	30-Aug-20	Sun
08.2020	31-Aug-20	Mon
08.2020	1-Sep-20	Tue
08.2020	2-Sep-20	Wed
08.2020	3-Sep-20	Thu
08.2020	4-Sep-20	Fri
08.2020	5-Sep-20	Sat
08.2020	6-Sep-20	Sun
etc.	etc.	etc.

Re: Create Master Calendar period from first Monday of the month until end week of th month

Kushal_Chawda — Mon, 21 Sep 2020 12:13:51 GMT

@dennysetiawan try below. Assuming Report_Date field is available in your data and it is in proper date format.

Reference_Date is created just to create other time parameters like year and quarters.

// Report_Date is the actual date field name in your data. Calendar_temp: Load Distinct MonthStart(Report_Date) as MonthStart, MonthEnd(Report_Date) as MonthEnd; LOAD FieldValue('Report_Date',RecNo()) as Report_Date AutoGenerate FieldValueCount('Report_Date'); Calendar: LOAD *, year(Reference_Date) as Year; LOAD *, WeekDay(WeekStart+IterNo()-1) as WeekDay, date(WeekStart+IterNo()-1) as Report_Date While WeekStart+IterNo()-1<=WeekEnd; LOAD date(MonthStart,'MMM YYYY') as Period, MonthStart as Reference_Date, if(MonthStart>WeekStart(MonthStart,0,0),WeekStart(MonthStart,1,0),WeekStart(MonthStart,0,0)) as WeekStart, WeekEnd(MonthEnd,0,0) as WeekEnd Resident Calendar_temp; DROP Table Calendar_temp; DROP Fields WeekEnd,WeekStart;