10 Replies Latest reply: May 6, 2018 1:15 AM by Toni Kautto RSS

    Timestamp function

    Shakeeb Mohammed

      Hi,

       

      Could someone help me with the following script please -

       

      if(Timestamp([Confirmed Arrival Time])>Timestamp([PTA]),1,0) as LatePTA,

           if(Timestamp([Confirmed Arrival Time])<Timestamp([PTA]),1,0) as OnTimePTA,

       

      I'm trying to add a statement that will say if the confirmed arrival time is more or less than 30 minutes of the PTA mark it as OnTimePTA

       

      I am using Qliksense - Thanks in advance!

        • Re: Timestamp function
          Fernanda Gomez

          May be like this

               if(Timestamp([Confirmed Arrival Time])<Timestamp([PTA]) and Timestamp([PTA]) - Timestamp([Confirmed Arrival Time]) <= MakeTime(0, 30),1,0) as OnTimePTA,

          • Re: Timestamp function
            Toni Kautto

            Does Timestamp([Confirmed Arrival Time]) and Timestamp([PTA]) return the timestamp values that you expect?

            If not, you need to format the incoming value properly. Please confirm what format your data source has for theses fields.

            If these work, then you do not need to use the timestamp format functions, as the comparison is don the on the timestamps existing numerical representation.

             

            Time is defined as a fraction of a day. There are 24 hours in a day, and each hour contains of 60 minutes. One minute is there for 1/(24*60) = 1/1440, meaning that 30 minutes is 30/1440.

             

            Adjusting a timestamp or time is simply done by adding or subtracting time. The below comparison would return true if [Confirmed Arrival Time] is larger than PTA and smaller than or equal to PTA+30 min. Is this what you are asking for?

             

            [Confirmed Arrival Time] > [PTA] AND [Confirmed Arrival Time] <= [PTA]+(30/1440)

              • Re: Timestamp function
                Shakeeb Mohammed

                Hi,

                 

                i had tried the following -

                 

                  [Confirmed Arrival Time] > [PTA] AND [Confirmed Arrival Time] <= [PTA]+(30/1440) as OnTimePTA2,

                  [Confirmed Arrival Time] < [PTA] AND [Confirmed Arrival Time] >= [PTA]-(30/1440) as LatePTA2,

                 

                But no luck ,

                 

                The fields Confirmed time arrival and PTA are formatted to return the correct timestamp.

                 

                PTA,

                     Date(left(PTA,10))as PlanDate,

                     WeekStart(Date(left(PTA,10))) as Week,

                     Timestamp(PTA) as TS_PTA,

                     Timestamp([Confirmed Arrival Time]) as TS_ConfirmedArrivalTime,

                 

                I may not explained the question properly.

                 

                I'm trying to add a statement that will say if the confirmed arrival time is more or less than 30 minutes of the PTA mark it as OnTimePTA –

                 

                and if the Confirmed time arrival + 30mins is more than the PTA mark it as LatePTA.

                 

                E.g. – Planned time arrival is 13:00 – arrives at 13:15 but because this is within the 30 minute window it will still be on time. But if it arrives at 13:35 it will mark it as Late.

              • Re: Timestamp function
                Shakeeb Mohammed

                Thank you all for the help!

                 

                I've managed to get this resolved.

                 

                The Script i used was -

                 

                if(num#(interval(Timestamp([Confirmed Arrival Time],'hh:mm:ss') - Timestamp([PTA],'hh:mm:ss'),'m')) <= 30,1,0) as OnTimePTA,

                 

                if(num#(interval(Timestamp([Confirmed Arrival Time],'hh:mm:ss') - Timestamp([PTA],'hh:mm:ss'),'m')) > 30,1,0) as LatePTA

                  • Re: Timestamp function
                    Toni Kautto

                    Great to hear that you found the formula you where looking for!

                     

                    I would recommend reviewing the expression, since I think you have introduced a few unnecessary formatting steps in your example. Something like this should give the same result;

                     

                    if(interval([Confirmed Arrival Time] - [PTA]) <= 30/(60*24),1,0) as OnTimePTA

                    if(interval([Confirmed Arrival Time] - [PTA]) > 30/(60*24),1,0) as LatePTA

                     

                    ---

                     

                    For calculations and comparisons all processing is done on the underlying numerical value of your data point. This means that all values are treated as Dual values, with a visual text part and an underlying numerical value.

                     

                    The formatting Timestamp([Confirmed Arrival Time],'hh:mm:ss') will actually only change the way the timestamp is presented, the underlying numerical value will still contain the exact date and time. IF you wanted to convert the timestamp to a time the proper syntax would be Time(Frac([Confirmed Arrival Time])), so that the integer representing the date is removed by only keeping the fraction of the underlying numerical value.

                     

                    Interval() calculates the time between to point in time, which means it will aggregate the difference of the numerical value of our timestamp. There is no need to reformat that timestamp prior to this operation.

                     

                    if(num#(interval([Confirmed Arrival Time] - [PTA],'m')) <= 30,1,0) as OnTimePTA

                    if(num#(interval([Confirmed Arrival Time] - [PTA],'m')) > 30,1,0) as LatePTA

                     

                    As I mentioned above time is a fraction, which also applies for the result of Interval (). The result will be a fraction representing the interval. The format to minute in above example, again only changes the visual value and does not alter the underlying fraction.

                    30 minutes of a 24 hour day with 60 minutes per hour can be represented as the fraction 30 / (60 * 24). Instead of forcing your interval into an integer you can simply compare with the 30 minute fraction. Which leads you to the optimized expressions;

                     

                    if(interval([Confirmed Arrival Time] - [PTA]) <= 30/(60*24),1,0) as OnTimePTA

                    if(interval([Confirmed Arrival Time] - [PTA]) > 30/(60*24),1,0) as LatePTA