Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .....
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;
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: