1 Reply Latest reply on Aug 9, 2017 3:29 AM by Vincent C

# Calculate an Expected Delivery Date From Creation Date

Hello Everyone,

I am new in Tableaux and I try to redo what I used to do manually in Excel in an automatic way in tableau.

I am trying to create an "expected" delivery date based on the order Creation Date with multiple criteria. Indeed, we have 2 level of Priority.

1. urgent (delivery following day)

2. Standard (delivered within 5 days)

It seems pretty simple from here, however I need to add a cutt off, for instance, the orders created before 18:00:00 are before the cut off so it would be more like below:

1. urgent before 18:00:00 (delivery following day)  - Expected delivery = Order creation date + 1

1. urgent after 18:00:00 (delivery within 2 days)  - Expected delivery = - Order creation date + 2

2. Standard before 18:00:00 (delivered within 5 days) - Expected delivery =- Order creation date + 5

2. Standard after 18:00:00 (delivered within 6 days) - Expected delivery = - Order creation date + 6

I classified all the order created before 6pm as 0 and all the order created at and after 6 PM as 1

I would then want to know of I can manage this, logic as below

IF "Order Prioritity" = Urgent and "Order Creation Date" < 6PM  THEN "Order Creation Date" + 1 day

IF "Order Prioritity" = Urgent and "Order Creation Date" => 6PM  THEN "Order Creation Date" + 2 day

IF "Order Prioritity" = Standard and "Order Creation Date" < 6PM  THEN "Order Creation Date" + 5 day

IF "Order Prioritity" = Standard and "Order Creation Date" => 6PM  THEN "Order Creation Date" + 6 day

Regards

ben

• ###### 1. Re: Calculate an Expected Delivery Date From Creation Date

Hi Benjamin,

Maybe something like this:

[6PM]:

IF DATEPART('hour', [Creationdate]) >=18 THEN 'After' ELSE 'Before' END

[Expected delivery]:

IF [Order Priority] = 'Urgent' THEN IF [6PM] = 'After' THEN DATEADD('day', 2, DATE([Order Date])) ELSE DATEADD('day', 1, DATE([Order Date])) END

ELSE IF [6PM] = 'After' THEN DATEADD('day', 6, DATE([Order Date])) ELSE DATEADD('day', 5, DATE([Order Date]))  END END

Hope this helps.

Regards,

Vincent