How to use Master Calendar with two or more different dates from different table

[qlikviewforum]

I have OrderDate in Order table and DispatchDate in Dispatch table and etc... How can make use of Master Calendar in his scenario. Please let me know your idea.

[pradosh_thakur · Accepted solution]

HAve a look

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

https://community.qlik.com/t5/QlikView-Documents/Linking-to-two-or-more-dates/ta-p/1495322

[rahulpawarb]

May be this:

Order Table has OrderDate & OrderId and Dispatch Table has DispatchDate & DispatchId fields then create a bridge table; this will be linked to Master calendar and Order as well as Dispatch tables.

DateBridge:
LOAD DISTINCT
OrderId,
OrderDate AS Date,
'Order' AS DateType
Resident Order
Where ISNULL(OrderDate) = 0;
Concatenate
LOAD DISTINCT
DispatchId,
DispatchDate AS Date,
'Dispatch' AS DateType
Resident Dispatch
Where ISNULL(DispatchDate) = 0;

Use below sort of set analysis expression:

Count({<DateType={'Order'}, Date={">=$(=MonthStart(AddMonths(Max(Date), -11))) <=$(=Max(Date))"}>}OrderId)

Count({<DateType={'Dispatch'}, Date={">=$(=MonthStart(AddMonths(Max(Date), -11))) <=$(=Max(Date))"}>}DispatchId)