Help on Max, Median Percentile

[#1]

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

[#2]

Hi Jacob

Thanks but I cant open it,

[#3]

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?

[#4]

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

[hic]

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

[jagannalla]

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