14 Replies Latest reply: May 23, 2014 5:05 AM by Jagan Nalla RSS

    Help on Max, Median Percentile

      HI,

       

      I need to create a table which calculates the difference between an arrival time and a departure time and then shows the Max, median and 95% in hh:mm:ss

        • Re: Help on Max, Median Percentile
          Dariusz Mielczarek

          you should use max() and fractile() functions.

           

          regards

          Darek

          • Re: Help on Max, Median Percentile

            max(arrival time - departure time)

            median(arrival time - departure time)

            fractile(arrival time - departure time, .95)

              • Re: Help on Max, Median Percentile

                Hi Jacob,

                 

                yep tried that, it comes out blank

                  • Re: Help on Max, Median Percentile


                    I've just changed the time format from hh:mm:ss to DD/MM/YYYY hh:mm:ss and Its not blank anymore, but it is rubbish.

                    • Re: Help on Max, Median Percentile

                      Could I see your data?

                        • Re: Help on Max, Median Percentile
                          DepartmentArrival DateArrival TimeDeparture DateDeparture Time
                          115/05/201400:13:00:000                  15/05/201402:18:00:000                 
                          115/05/201400:14:00:000                  15/05/201400:50:00:000                 
                          115/05/201400:36:00:000                  15/05/201404:30:00:000                 
                          115/05/201400:44:00:000                  15/05/201402:55:00:000                 
                          115/05/201401:02:00:000                  15/05/201404:14:00:000                 
                          115/05/201401:12:00:000                  15/05/201403:10:00:000                 
                          115/05/201401:26:00:000                  15/05/201403:44:00:000                 
                          115/05/201401:29:00:000                  15/05/201403:06:00:000                 
                          115/05/201401:33:00:000                  15/05/201404:38:00:000                 
                          115/05/201401:52:00:000                  15/05/201402:44:00:000                 
                          115/05/201402:19:00:000                  15/05/201405:57:00:000                 
                          115/05/201402:23:00:000                  15/05/201404:00:00:000                 
                          115/05/201402:47:00:000                  15/05/201404:10:00:000                 
                          115/05/201402:54:00:000                  15/05/201404:25:00:000                 
                            • Re: Re: Help on Max, Median Percentile

                              Okay, I think I got it. I used subfields and some calculations to find the interval length.

                               

                              See the attached file.

                                • Re: Help on Max, Median Percentile

                                  Hi Jacob

                                   

                                  Thanks but I cant open it,

                                    • Re: Help on Max, Median Percentile

                                      HI Jacob,

                                       

                                      I've sorted that problem, but you solution just leave me with the Max:, or Median:, or Fractile in the place where I want the answer in hh:mm  .

                                       

                                      I know I'm being thick but what am I doing wrong?

                                       


                                        • Re: Re: Help on Max, Median Percentile
                                          Dariusz Mielczarek

                                          Julie,

                                           

                                          i hope it will be helpful.

                                          DateTime type has string and number representation. Calculations are made on number format. You may change format usting time() or choosing time on number tab of your chart.

                                          But first it is very important to read data in format, which will be possible to interpret as date/time.

                                           

                                          regards

                                          Darek

                                          • Re: Help on Max, Median Percentile
                                            Henric Cronström

                                            Read the following. It explains how QlikView manages date and time fields.

                                            QlikView Date fields

                                             

                                            The bottom line is anyhow that you need to interpret the strings as timestamps in the script, using one of the following functions: Timestamp#(), Date#(), Time#(). Note the hash signs. This will assign a numeric value to your timestamps.

                                             

                                            Then you need to wrap the above - or your median, min, max calculation - in a formatting function to make it look nice, using Timestamp(), Date(), Time().

                                             

                                            HIC

                                            • Re: Re: Help on Max, Median Percentile
                                              Jagan Nalla

                                              Hi,

                                               

                                              Hope it helps you.

                                               

                                              T1:

                                              LOAD *,

                                              Time(fabs(Interval(time#([Arrival Time],'hh:mm:ss:fff')-time#([Departure Time],'hh:mm:ss:fff'))),'hh:mm:ss:fff')as TimeInterval;

                                              LOAD * INLINE [

                                                  Department, Arrival Date, Arrival Time, Departure Date, Departure Time

                                                  1, 15/05/2014, 00:13:00:000                 , 15/05/2014, 02:18:00:000                

                                                  1, 15/05/2014, 00:14:00:000                 , 15/05/2014, 00:50:00:000                

                                                  1, 15/05/2014, 00:36:00:000                 , 15/05/2014, 04:30:00:000                

                                                  1, 15/05/2014, 00:44:00:000                 , 15/05/2014, 02:55:00:000                

                                                  1, 15/05/2014, 01:02:00:000                 , 15/05/2014, 04:14:00:000                

                                                  1, 15/05/2014, 01:12:00:000                 , 15/05/2014, 03:10:00:000                

                                                  1, 15/05/2014, 01:26:00:000                 , 15/05/2014, 03:44:00:000                

                                                  1, 15/05/2014, 01:29:00:000                 , 15/05/2014, 03:06:00:000                

                                                  1, 15/05/2014, 01:33:00:000                 , 15/05/2014, 04:38:00:000                

                                                  1, 15/05/2014, 01:52:00:000                 , 15/05/2014, 02:44:00:000                

                                                  1, 15/05/2014, 02:19:00:000                 , 15/05/2014, 05:57:00:000                

                                                  1, 15/05/2014, 02:23:00:000                 , 15/05/2014, 04:00:00:000                

                                                  1, 15/05/2014, 02:47:00:000                 , 15/05/2014, 04:10:00:000                

                                                  1, 15/05/2014, 02:54:00:000                 , 15/05/2014, 04:25:00:000       

                                              ];

                                               

                                               

                                              LOAD

                                              Max(TimeInterval) as Max,

                                              Min(TimeInterval) as Min,

                                              Time(Fractile(TimeInterval,.95),'hh:mm:ss:fff') as '95%'

                                              Resident T1;

                                               

                                              Thanks,

                                              Jagan