Order and Delivery date comparison

[Anonymous]

Hi, I would want to understand the relationship between customer_order_date and delivery_made_date, since data are in different sources so I have now concatenate them into below table structure:

Sales_Order_ID	Sales_Order_Date	Delivery_ID	Delivery_Date	Region	Product	Customer	SO_Value	DN_Value
100001	20181201			ABC	EFG	XYZ	100
100001		800001	20181207	ABC	EFG	XYZ		80
100001		800002	20181208	ABC	EFG	XYZ		20

My questions is i want to calculate the delivery date minus sales order date so that I would know the lead-time by different product / region / customer and whether it's impact by the SO_Value as well.

Please help and suggest how I can do it. Many thanks in advance for the help.

Regards,

Jacob

[laksyelugoti]

You may first want to repeat Sales_Order_Date for the rows or create a new field using LEFT JOIN like below which will give you a new column "Sales_Order_Date_NEW"

left join(Table1)

LOAD Sales_Order_ID

Sales_Order_Date as Sales_Order_Date_NEW

Resident Table1.

DROP FIELD Sales_Order_Date ;

you will have OUTPUT like this.

Sales_Order_ID	Sales_Order_Date_NEW	Delivery_ID	Delivery_Date	Region	Product	Customer	SO_Value	DN_Value
100001	20181201			ABC	EFG	XYZ	100
100001	20181201	800001	20181207	ABC	EFG	XYZ		80
100001	20181201	800002	20181208	ABC	EFG	XYZ		20

[Anonymous]

Thanks, it should be a good start, but then some order might have multiple delivery, that's why not sure how I can do it if I want to focus on the latest delivery date and also count the number of delivery for each order.

[Vegar]

If you adjust @laksyelugoti script like this

left join(Table1)
LOAD Sales_Order_ID
Max(Sales_Order_Date) as Last_Sales_Order_Date
Resident Table1
GROUP BY Sales_Order_ID;

You will get the last sales order date for all deliveries. To count deliveries per sales order you simply use count(Delivery_ID) or count(distinct Delivery_ID) as your chart expression.