You need to use Cannonical Date... Let me give you one example..
Consider that you have Sales Data where there are two dates... OrderDate and DeliveryDate....
Now requirement is to find Month wise TotalValue for Order Received and Order Delivered...
Use below script ...
===============================
Sales:
Load * Inline
[
OrderID, Customer, OrderDate, DeliveryDate, Price
ORD001, A, 05/01/2014, 10/01/2014, 120
ORD002, B, 07/01/2014, 12/01/2014, 100
ORD003, A, 16/01/2014, 20/01/2014, 75
ORD004, C, 25/01/2014, 01/02/2014, 250
ORD005, D, 28/01/2014, 05/02/2014, 80
ORD006, B, 05/02/2014, 15/02/2014, 125
ORD007, B, 18/02/2014, 20/02/2014, 170
ORD008, C, 25/02/2014, 28/02/2014, 100
ORD009, B, 01/03/2014, 10/03/2014, 90
ORD010, A, 04/03/2014, 11/03/2014, 80
ORD011, D, 15/03/2014, 28/03/2014, 200
ORD012, C, 20/03/2014, 01/04/2014, 240
ORD013, A, 25/03/2014, 05/04/2014, 210
ORD014, E, 28/03/2014, 18/04/2014, 340
ORD015, B, 01/04/2014, 10/04/2014, 70
ORD016, D, 11/04/2014, 15/04/2014, 60
ORD017, E, 15/04/2014, 20/04/2014, 50
ORD018, C, 18/04/2014, 22/04/2014, 40
ORD019, B, 23/04/2014, 28/04/2014, 140
ORD020, E, 25/04/2014, 30/04/2014, 100
];
Mapping_OrderDate:
Mapping Load
OrderID,
OrderDate
Resident Sales;
Mapping_DeliveryDate:
Mapping Load
OrderID,
DeliveryDate
Resident Sales;
DataBridge:
Load
OrderID,
ApplyMap('Mapping_OrderDate',OrderID) as CannonicalDate,
'Order' as DataType
Resident Sales;
Load
OrderID,
ApplyMap('Mapping_DeliveryDate',OrderID) as CannonicalDate,
'Delivery' as DataType
Resident Sales;
Join
Load CannonicalDate, Month(CannonicalDate) as CannonicalMonth Resident DataBridge;
===============================
Now create a Straight Table
Dimension
CannonicalMonth
Expression
SUM({<DataType = {'Order'}>}Price)
SUM({<DataType = {'Delivery'}>}Price)
Hope this helps...
HAPPY QLIKING !!
