Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: 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