Canonical Date between two ID and two dates

[Vince_CH]

Dear Community, I have a situation like attached picture indicated, with offer number and offer date, which is partially converted into real orders. there are also orders directly received without offer numbers, for each order there are order date too. My question is: since [offer number] is different from [order no.], or only partly connected in this case, if I want to have a canonical date to show in same period to compare the total amount and total order value, how shall I do it in the script writings? thanks! @hic

[Vince_CH]

Hello Experts, 

Is there anyone who can kindly assist on this problem?  Look foward to your valuable suggestions and thanks in advance.

@hic  @sunny_talwar  @Gysbert_Wassenaar  @pradosh_thakur 

I had done followiing steps now so far to link the datetype in the scripts:

DateBridge:

     Load

          [Order No.],

          [Order Date] as CanonicalDate,

          'Order' as DateType

          Resident [Sales];

Concatenate (DateBridge)

     Load

         [Offer No.],

         [Enquiry Date] as CanonicalDate,

          'Enquiry' as DateType

          Resident [Sales];

Concatenate (DateBridge)

     Load

          [Offer No.],

          [Offer Date] as CanonicalDate,

          'Offer' as DateType

          Resident [Sales];

Concatenate (DateBridge)

     Load  

          [Order No.],

          [OrdDelNot_Goods_Issue_Date_actual]  as CanonicalDate,

          'Delivery' as DateType

          Resident [Sales];

Concatenate (DateBridge)           

      Load

          [Order No.],

          [INVOICE_Date] as CanonicalDate,

          'Invoice' as DateType

          Resident [Sales];

Concatenate (DateBridge)

Load   

          [Order No.],

          Orderfinancedate as CanonicalDate,

          'Orderfinanceinvoice' as DateType

          Resident Financeordercost;

Datelink:      

Load

[Order No.]&[Offer No.] as key2,

CanonicalDate,

DateType,

Year(CanonicalDate) as Year_Can,

Month(CanonicalDate) as Month_Can,

……

Resident DateBridge;

Drop table DateBridge;

And the data module looks like below picture indicates now, but with the datetype used in apps expression, the offeramount is not correct as about 7.5 million in 2019 Q2, actually it is correct to be about 10 million as right side bottom chart indicated. but I don't know where is the problem??

[Vince_CH]

Hello All, 

For the offers and orders, I had concanated them in the scripts earlier. and I just checked some offer nos as below and noticed that the offer no and order number and related dates are like below.  I am not sure if it is the reason causing the problem as previous decribed. But how shall eliminate this disorder here? shall I use left join instead of concanate to join the offer table with order table? or? 

[Vince_CH]

Dear friends, are there any experts can give some hints here?
thanks in advance!