4 Replies Latest reply on Sep 26, 2016 8:27 AM by kettan

# Calculated field

Hi All,

I have two columns in order table, “orderDate (Timestamp)” and “deliverydate (Timestamp)”.

I want to create a calculated field for number of records according to:

If order is before 3pm and delivery is on same day OR order is after 3pm and delivery on not later than next day.

Thnaks

Qamar

• ###### 1. Re: Calculated field

Hi Qamar,

I'm not entirely sure on the structure of your date fields but try this:

IF DATEPART('hour',[orderDate (Timestamp)])<15 and DATETRUNC('day',[orderDate (Timestamp)])=DATETRUNC('day',[deliverydate (Timestamp)]) THEN [Number of Records]

ELSEIF DATEPART('hour',[orderDate (Timestamp)])>=15 and DATETRUNC('day',[orderDate (Timestamp)])>=DATETRUNC('day',[deliverydate (Timestamp)]) and DATETRUNC('day',[orderDate (Timestamp)])<=(DATETRUNC('day',[deliverydate (Timestamp)])+1) THEN [Number of Records]

END

• ###### 2. Re: Calculated field

here is the structure of my data.

The datatype of orderdate and deliverydate is "Datetime" • ###### 3. Re: Calculated field

Looks good. I think I had an error in my first calculation. Try this one:

IF DATEPART('hour',[orderdate])<15 AND DATETRUNC('day',[orderdate])=DATETRUNC('day',[deliveryDate]) THEN [Number of Records]

ELSEIF DATEPART('hour',[orderdate])>=15 AND DATETRUNC('day',[deliveryDate])>=DATETRUNC('day',[orderdate]) AND DATETRUNC('day',[deliveryDate])<=(DATETRUNC('day',[orderdate])+1) THEN [Number of Records]

END

• ###### 4. Re: Calculated field

We can also use numeric calculations:

IF ( FLOAT([orderdate]) % 1 <  .625 AND INT([orderdate]) = INT([deliveryDate]) ) OR

( FLOAT([orderdate]) % 1 >= .625 AND INT([deliveryDate]) - INT([orderdate]) <=  1 )

THEN "Order is before 3pm and delivery is on same day OR after 3pm and delivery not later than next day"

ELSE "Late delivery"

END

% 1   returns the decimals (time only).

.625  is the numeric value of 3pm (15/24).