Canonical & Master Calendars for 2 Tables with Date fields

RobinTMavhu — Wed, 16 Apr 2025 10:07:39 GMT

l created a Canonical & Master Calendar l was sure would work but apparently it only worked for 1 table i.e. i created a bar graph and one expression worked and for the second table it failed to calculate correctly or slice the data by month. If anyone has a code which works where its joining date fields from two different tables please kindly share your syntax. Mine is below and maybe you can pick my errors

DateBridge:
Load
[Posting_Date] as Flag_Date,
[Material],
[Posting_Date] as Canonical_Date,
'Inventories' as DateType
Resident [Inventories];

Concatenate

Load
[Date] as Flag_Date,
[Material],
[Date] as Canonical_Date,
'Materials_usage' as DateType
Resident [Materials_usage];

// Master Calendar aligned to your Canonical Calendar

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET DateFormat='YYYY-MM-DD';
SET TimeFormat='hh:mm:ss';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

// Determine the min and max dates from your DateBridge
TempMinMax:
LOAD
Min([Canonical_Date]) AS MinDate,
Max([Canonical_Date]) AS MaxDate
RESIDENT DateBridge;

LET vMinDate = Num(Peek('MinDate', -1, 'TempMinMax'));
LET vMaxDate = Num(Peek('MaxDate', -1, 'TempMinMax'));
DROP TABLE TempMinMax;

// Generate a continuous date range
TempCalendar:
LOAD
$(vMinDate) + IterNo() - 1 AS Num,
Date($(vMinDate) + IterNo() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

MasterCalendar:
LOAD
TempDate AS Date, // Renamed for clarity and consistency
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) AS Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,
Year(TempDate) & '-' & Month(TempDate) AS YearMonth,
WeekDay(TempDate) AS WeekDay,
DayName(TempDate) AS DayName,
MonthName(TempDate) AS MonthName,
'Q' & Ceil(Month(TempDate) / 3) AS QuarterOnly, // Added for consistency with typical Master Calendars
Year(TempDate) & '-Q' & Ceil(Month(TempDate) / 3) AS YearQuarter, // Added for consistency
Date(MonthStart(TempDate), 'YYYY-MM-DD') AS MonthStart, // Added for consistency
Date(MonthEnd(TempDate), 'YYYY-MM-DD') AS MonthEnd, // Added for consistency
Date(WeekStart(TempDate), 'YYYY-MM-DD') AS WeekStart, // Added for consistency
Date(WeekEnd(TempDate), 'YYYY-MM-DD') AS WeekEnd, // Added for consistency
Date(YearStart(TempDate), 'YYYY-MM-DD') AS YearStart, // Added for consistency
Date(YearEnd(TempDate), 'YYYY-MM-DD') AS YearEnd, // Added for consistency
Num(TempDate) AS DateNumber, // Added for consistency
Dual(MonthName(TempDate), Month(TempDate)) AS MonthSort, // Added for consistent sorting
Dual(DayName(TempDate), WeekDay(TempDate)) AS DaySort // Added for consistent sorting
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Canonical & Master Calendars for 2 Tables with Date fields

Oleg_Troyansky — Wed, 16 Apr 2025 13:29:45 GMT

Hi Robin,

The only issue I see in your script is that your date in the date bridge table is called Canonical_Date, while the Master Calendar has the field Date, so if this is your script verbatim, then nothing should link, and no expression should work right.

Other than that, I'd need to see your expressions to understand what's not working right.

Cheers,

topic Re: Canonical & Master Calendars for 2 Tables with Date fields in App Development

Canonical & Master Calendars for 2 Tables with Date fields

Re: Canonical & Master Calendars for 2 Tables with Date fields