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

[dennysetiawan]

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?

[tresesco]

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

[dennysetiawan]

Hi @tresesco , 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.

[Kushal_Chawda · Accepted solution]

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