3 Replies Latest reply: Aug 31, 2011 3:16 AM by Stefan Wühl RSS

    Conditions based on time stamps



      I have 2 columns


      1 - releasedate(without time stamp)

      2- Shiptime ( date with time stamp)


      I have to have a condition in my data such that ...i need to count the id's based on shiptime and release date


      If the release is on say Aug 30 , and ship time is one day before aug 30, that is aug 29th and after 1:00pm , then put it in 'with in 1 day',if ship time is one or more days before 1 pm , then put it in '1 day or more'


      How do i write this condition for a pie chart ?


      I am bit confused ..

        • Re: Conditions based on time stamps
          Stefan Wühl

          If I understood your problem correctly, I would use something like this as flag in the script or as calculated dimension:


          if( (ReleaseDate - ShipTimestamp) <= 11/24, 'within 1 day ','1 day or more ')


          Dates and Timestamps are numericals in principal, so you can just subtract two dates / timestamps (a timestamp is a date with decimal places). 11/24 is indicating 11 hours, which is the difference between midnight and 1:00 pm the day before, right?

          (I noticed though that a Shiptimestamp of exactely 1 pm on Aug 29th and a release date of 30 Aug lead to 1 day or more, one second later and it fell within 1 day).

            • Conditions based on time stamps

              This gives me correct answers , thank you very very much , you made my expression so simple , i have been trying multiple ways with if statements in the scripts.

              Could you please explain what is 24 doing in the expression? i understood 11 is the difference between midnight and 1:00pm , and how are we getting correct values by dividing 24 ?



              Thanks a ton.

                • Conditions based on time stamps
                  Stefan Wühl

                  Hours are represented as decimal places / fractional part of a timestamp value, where the whole integer number represents days (1 per day). If a difference of 1 represents one day, 1/24 represents one hour (24 hours a day).


                  I noticed also that if your shipment is later than release date, it will still count to within 1 day, you could catch that if you want with an additional if.