topic Re: Help creating a master calendar with equal month or periods in App Development

Help creating a master calendar with equal month or periods

hgreen — Fri, 10 Feb 2023 14:41:00 GMT

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.

Re: Help creating a master calendar with equal month or periods

SerhanKaraer — Tue, 14 Feb 2023 06:16:38 GMT

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;

Re: Help creating a master calendar with equal month or periods

marcus_sommer — Tue, 14 Feb 2023 07:48:02 GMT

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