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

# 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

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

regards

Darek

• ###### Re: Help on Max, Median Percentile

Thank you

I've found those. But I need to do the initial calculation first - Difference between arrival and departure time

11:30 - 09:15 = 01:45 for a large number of people and then find teh Max

• ###### 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
 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
• ###### 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

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

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

Hi,

Hope it helps you.

T1:

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

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

];

Max(TimeInterval) as Max,

Min(TimeInterval) as Min,

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

Resident T1;

Thanks,

Jagan