topic Re: Master Calendar Help? in Visualization and Usability

Master Calendar Help?

wgoudeau — Wed, 02 Mar 2022 21:07:37 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;

Re: Master Calendar Help?

micheledenardi — Thu, 03 Mar 2022 07:20:56 GMT

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

Re: Master Calendar Help?

wgoudeau — Thu, 03 Mar 2022 14:31:36 GMT

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?

Re: Master Calendar Help?

micheledenardi — Fri, 04 Mar 2022 07:12:57 GMT

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 .....

Re: Master Calendar Help?

wgoudeau — Mon, 07 Mar 2022 16:40:58 GMT

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
;