Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner - Contributor III
Partner - Contributor III

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

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?

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

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

Screenshot 2020-09-21 130149.pngScreenshot 2020-09-21 130214.png

 

View solution in original post

3 Replies
tresesco
MVP
MVP

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

dennysetiawan
Partner - Contributor III
Partner - Contributor III
Author

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

PeriodDate ReportDay Report
07.20206-Jul-20Mon
07.20207-Jul-20Tue
07.20208-Jul-20Wed
07.20209-Jul-20Thu
07.202010-Jul-20Fri
07.202011-Jul-20Sat
07.202012-Jul-20Sun
07.202013-Jul-20Mon
07.202014-Jul-20Tue
07.202015-Jul-20Wed
07.202016-Jul-20Thu
07.202017-Jul-20Fri
07.202018-Jul-20Sat
07.202019-Jul-20Sun
07.202020-Jul-20Mon
07.202021-Jul-20Tue
07.202022-Jul-20Wed
07.202023-Jul-20Thu
07.202024-Jul-20Fri
07.202025-Jul-20Sat
07.202026-Jul-20Sun
07.202027-Jul-20Mon
07.202028-Jul-20Tue
07.202029-Jul-20Wed
07.202030-Jul-20Thu
07.202031-Jul-20Fri
07.20201-Aug-20Sat
07.20202-Aug-20Sun
08.20203-Aug-20Mon
08.20204-Aug-20Tue
08.20205-Aug-20Wed
08.20206-Aug-20Thu
08.20207-Aug-20Fri
08.20208-Aug-20Sat
08.20209-Aug-20Sun
08.202010-Aug-20Mon
08.202011-Aug-20Tue
08.202012-Aug-20Wed
08.202013-Aug-20Thu
08.202014-Aug-20Fri
08.202015-Aug-20Sat
08.202016-Aug-20Sun
08.202017-Aug-20Mon
08.202018-Aug-20Tue
08.202019-Aug-20Wed
08.202020-Aug-20Thu
08.202021-Aug-20Fri
08.202022-Aug-20Sat
08.202023-Aug-20Sun
08.202024-Aug-20Mon
08.202025-Aug-20Tue
08.202026-Aug-20Wed
08.202027-Aug-20Thu
08.202028-Aug-20Fri
08.202029-Aug-20Sat
08.202030-Aug-20Sun
08.202031-Aug-20Mon
08.20201-Sep-20Tue
08.20202-Sep-20Wed
08.20203-Sep-20Thu
08.20204-Sep-20Fri
08.20205-Sep-20Sat
08.20206-Sep-20Sun
etc.etc.etc.

 

 

Kushal_Chawda

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

Screenshot 2020-09-21 130149.pngScreenshot 2020-09-21 130214.png