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]

I would suggest this approach:

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');

This solution comes from Rob Wunderlich, one of the most qlik script expert.

In below article it explain why this solution is more efficient than others: 

BETTER CALENDAR SCRIPTS 

[wgoudeau]

Thank you for your response.

I have [Posted Date] and [Transaction Date]. I would like to generate month, month year and year for both. I am assuming to use only one date [Posted Date] as my master calendar. Is there a way to convert [Transaction Date] into month, month year and year without adding a second master calendar?

[micheledenardi]

Sure, 

LOAD
Region,
Company,
"Report Name",
"Expense Amount (USD)",
"Posted Date",
"Transaction Date",
year("Transaction Date") as "Transaction Year",
Month("Transaction Date") as "Transaction Month",
Monthname("Transaction Date") as "Transaction Monthname",
num(month("Transaction Date"),'00')&'/'&year("Transaction Date") as "Transaction Period"
FROM .....

[wgoudeau]

I added Month-Year to the master calendar. Does this look correct to you?

MasterCalendar:
Load
TempDate AS [Posted Date],
week(TempDate) As PostedWeek,
Year(TempDate) As PostedYear,
Month(TempDate) As PostedMonth,
Day(TempDate) As PostedDay,
'Q' & ceil(month(TempDate) / 3) AS PostedQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as PostedWeekYear,
num(Month("TempDate"),'00')&'/'&year("TempDate") as PostedMonthYear,

//ADDED ABOVE FOR MONTH YEAR

WeekDay(TempDate) as PostedWeekDay
;