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

    Calculated field

    qamar zaman

      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.

       

      Any help please.

       

      Thnaks

      Qamar

        • 1. Re: Calculated field
          Stephen Lavery

          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
            qamar zaman

            hi Stephen Lavery,

             

            here is the structure of my data.

            The datatype of orderdate and deliverydate is "Datetime"

             

            • 3. Re: Calculated field
              Stephen Lavery

              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
                kettan

                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).