Master Calendar Help

wgoudeau — Wed, 02 Mar 2022 17:26:57 GMT

I have two dates - [Transaction Date] and [Posted Date] in the format of XX/XX/XXXX. I am trying to convert these dates to each have date, month, month year, date month year, etc...

While the below calendar I built works, I do not think it is correct. I need master calendar to show missing dates as well. Is there a better way to write it?

LOAD
Region,
Company,
"Report Name",
"Expense Amount (USD)",
"Transaction Date",
"Posted Date";
FROM .....

Calendar section-----------------------------------------------

LET vToday=num(today());
LET vMinDate=44197;
LET vMaxDate=num(today());

// Temporary Table

TempCal:
LOAD
date($(vMinDate) +RowNo()) as TempDate1,
date($(vMinDate) +RowNo()) as TempDate2
AutoGenerate
$(vMaxDate)-$(vMinDate);

//******************** MASTER CALENDAR**********************
MasterCalendar:

LOAD
TempDate1 as [Posted Date],

Year(TempDate1) AS [Posted_Year],
Month(TempDate1) as [Posted_Month],
day(TempDate1) as [Posted_MDay],
Week(TempDate1) as [Posted_Week],
WeekName(TempDate1) as [Posted_WeekName],
Date(MonthStart(TempDate1),'MM-YYYY') as [Posted_YearMonth],
//Resident TempCal;

TempDate2 as [Transaction_Date],

Year(TempDate2) AS [Transcation_Year],
Month(TempDate2) as [Transaction_Month],
day(TempDate2) as [Transaction_MDay],
Week(TempDate2) as [Transaction_Week],
WeekName(TempDate2) as [Transaction_WeekName],
Date(MonthStart(TempDate2),'MM-YYYY') as [Transaction_YearMonth]
Resident TempCal;

DROP Table TempCal;

topic Master Calendar Help in App Development

Master Calendar Help