Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
hgreen
Contributor
Contributor

Help creating a master calendar with equal month or periods

I am trying to create a second master calendar that it on an equal month/period schedule. Each 'month' needs to be 28 days in length starting on 2/26/23. I have not been able to find a way to implement this within my app. Below is the master calendar coding I have for my initial setup. I would like to either add the parameters to this calendar or create an additional calendar to properly show the data.

/******************** Table MinMaxTemp *******************/
MinMaxTemp:
LOAD
Min(FieldValue('Received Date',RecNO())) as MinDate,
Today() as MaxDate
AUTOGENERATE FieldValueCount('Received Date');

LET vToday = Num(Peek('MaxDate', 0, 'MinMaxTemp'));

/******************** MasterCalendarTemp *******************/

MasterCalendarTemp:
LOAD
Date(MinDate + IterNo() - 1) as [Received Date]
RESIDENT MinMaxTemp
WHILE MinDate + IterNo() - 1 <= MaxDate;

DROP TABLE MinMaxTemp;

/******************** CREATE MASTER CALENDAR *******************/
[Master Calendar]:
LOAD
[Received Date],
Week([Received Date]) as Week,
Year([Received Date]) as Year,
Month([Received Date]) as Month,
Day([Received Date]) as Day,
WeekDay([Received Date]) as WeekDay,
Dual('Q' & Ceil(Month([Received Date]) / 3), Ceil(Month([Received Date]) / 3)) as Quarter,
Date(MonthStart([Received Date]), 'M/YYYY') as MonthYear,
Week([Received Date]) & '-' &
     If(Week([Received Date]) = 1 AND Month([Received Date]) = 12, Year([Received Date]) + 1,
     If(Match(Week ([Received Date]), 52, 53) AND
     Month([Received Date]) = 1, Year([Received Date]) - 1, Year([Received Date]))) as WeekYear,
If(DayNumberOfYear([Received Date]) <= DayNumberOfYear($(vToday)), 1, 0 ) as IsInYTD,
If(DayNumberOfQuarter([Received Date]) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,
If(Day([Received Date]) <= Day($(vToday)), 1, 0) as IsInMTD,
If(Month([Received Date]) = Month($(vToday)), 1, 0) as IsCurrentMonth,
If(Month(AddMonths([Received Date],1)) = Month($(vToday)), 1, 0) as IsLastMonth

RESIDENT MasterCalendarTemp;

DROP TABLE MasterCalendarTemp;

 

I have also attached an excel file to show exactly what data I'm looking for.

Labels (2)
2 Replies
SerhanKaraer
Creator III
Creator III

Hello hgreen,

Here is a sample script for fiscal calendar from scratch.

SET DateFormat = 'DD.MM.YYYY';

LOAD StartDate, EndDate, Period, if(RecNo() = 1, FiscalYear, if(Period = 1, Peek('FiscalYear') + 1, Peek('FiscalYear'))) as FiscalYear;
LOAD If(RecNo() = 1, StartDate, Date(Peek('EndDate') + 1)) as StartDate,
	 If(RecNo() = 1, EndDate, Date(Peek('EndDate') + 28)) as EndDate,
     mod(Period - 1, 13) + 1 as Period,
     FiscalYear;
LOAD StartDate, Date(StartDate + 27) as EndDate, RecNo() as Period, Year(StartDate) as FiscalYear;
LOAD Date#('26.02.2023') as StartDate AutoGenerate 26;

 

marcus_sommer

4 weeks months sound like a kind of a 4-4-5 / 5-4-4 / 4-5-4 calendar. Within the following links are multiple examples how to create non-gregorian calendars including the above mentioned ones and also fiscal-calendars which have an offset to the normal calendars:

Non-Gregorian calendars - Qlik Community - 1471155

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741