Master Calendar - Month Year only

KyleR111 — Tue, 16 Aug 2022 15:52:44 GMT

Hi all,

I started building a master calendar based on the directions here in Understanding the Master Calendar

But, it makes the calendar go down to the day level when i just need month, month end to be specific.

My date field "report effective date" is only month ends

I tried editing the temp calendar section to be just month/year but keep getting an error from that section

Any help would be greatly appreciated 😄

Kyle

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min([Report Effective Date]) as minDate,
max([Report Effective Date]) as maxDate
Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD Month, Year(Month) as Year;
LOAD
Date(MonthStart($(vMin),IterNo()-1),'M/D/YYYY') as Month
AutoGenerate 1 While MonthStart($(vMin),IterNo()-1) <= $(vMax);

MasterCalendar:
Load
TempDate AS [Report Effective Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: Master Calendar - Month Year only

vikasmahajan — Wed, 17 Aug 2022 06:25:06 GMT

Hi Kyle,

you can create date using make date , create date this will create data for every 1 st of month if you don;t have full dates in your data.

MakeDate(Left(SALES_REPORTING_PERIOD,4), Mid(SALES_REPORTING_PERIOD, 5, 2), '01') as TransactionDate,

Create master calendar using TransactionDate

TEMP:

LOAD Max(TransactionDate) AS MaxDate

FROM your_QVD;
/**********************Select Min and Max Date from 'TEMP' Table*************/
LET varMinDate = Num(MakeDate(2016,06,01)); //Num(peek('MinDate', 0, 'TEMP')); // Min Date setting
LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));
LET vToday = Num(today());
let vMaxMonth = num(month(peek('MaxDate', 0, 'TEMP')));
let vMaxMonthName = month(peek('MaxDate', 0, 'TEMP'));

DROP TABLE TEMP;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS %Date,

Date(TempDate,'DD/MM/YYYY') As TransactionDate,
round(12*($(varMaxDate)-num(TempDate))/365.25) as MonthsAgo,
YearName(TempDate,0,1) AS FinancialYear,
Date(Yearstart(TempDate,0,1)) AS YearStart,
Date(Yearend(TempDate,0,1)) AS YearEnd,
Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
Month(TempDate) AS CALENDAR_Month,
Mod(Num(Month(TempDate)),3) + If(Mod(Num(Month(TempDate)),3)=0, 3, 0) as CALENDAR_Month_3MRolling,
Num(Month(TempDate)) AS MonthNumber,
MonthStart(TempDate) AS MonthStart,
MonthEnd(TempDate) AS MonthEnd,
Year(TempDate) AS Year,
YEAR(TempDate) AS CALENDAR_YEAR,
'Q' & CEIL(NUM(MONTH(TempDate))/3) AS CALENDAR_QUARTER,
YEAR(TempDate) &'-'& 'Q' & CEIL(NUM(MONTH(TempDate))/3) AS CALENDAR_QUARTER_YEAR,
'Q' & CEIL(NUM(MONTH(TempDate))/3)&'-'&monthname(TempDate) AS CALENDAR_QUARTER_MONTH,
//'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
'Q' & Ceil(Month(TempDate) / 3) AS Quarter,
quarterstart(TempDate,0,1) AS QuarterStart,
quarterend(TempDate,0,1) AS QuarterEnd,
quartername(TempDate,0,1) AS QuarterName,
Year(TempDate) AS FiscalYear,
AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
NUM(TempDate) AS DateNumSeq,
monthname(TempDate) AS CalMonthYear,
Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,
num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection
num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection
num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS

RESIDENT TempCalendar where MonthStart(TempDate) = TempDate
ORDER BY TempDate Asc;

DROP Table TempCalendar;

Hope this help you.

Vikas

topic Master Calendar - Month Year only in Visualization and Usability

Master Calendar - Month Year only

Re: Master Calendar - Month Year only