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

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

qlikviewforum — Sat, 16 Nov 2024 21:21:23 GMT

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.

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

pradosh_thakur — Mon, 25 Feb 2019 10:30:53 GMT

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

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

rahulpawarb — Mon, 25 Feb 2019 10:35:08 GMT

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)