Master Calendar Help

[wgoudeau]

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;

[micheledenardi]

Try this solution offered by Rob: 

MasterCalendar: 
Load 
 TempDate AS OrderDate, 
 week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter, 
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
 WeekDay(TempDate) as WeekDay 
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('OrderDate', recno()))-1 as mindate,
 max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');

Here the full thread:

BETTER CALENDAR SCRIPTS