topic Help on Max, Median Percentile in QlikView

Help on Max, Median Percentile

Thu, 22 May 2014 14:17:20 GMT

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

Thu, 22 May 2014 14:26:26 GMT

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

regards

Darek

Re: Help on Max, Median Percentile

Thu, 22 May 2014 14:29:35 GMT

max(arrival time - departure time)

median(arrival time - departure time)

fractile(arrival time - departure time, .95)

Re: Help on Max, Median Percentile

Thu, 22 May 2014 14:30:30 GMT

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

Thu, 22 May 2014 14:34:15 GMT

Hi Jacob,

yep tried that, it comes out blank

Re: Help on Max, Median Percentile

Thu, 22 May 2014 15:02:45 GMT

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

Thu, 22 May 2014 15:04:17 GMT

Could I see your data?

Re: Help on Max, Median Percentile

Thu, 22 May 2014 15:12:01 GMT

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

Thu, 22 May 2014 15:43:07 GMT

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

Thu, 22 May 2014 17:09:52 GMT

Julie, attach sample

22-05-2014 17:04 użytkownik "Julie Teasdale" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Help on Max, Median
Percentile
reply from Julie Teasdale<http://community.qlik.com/people/craftywytche?et=watches.email.thread>in *Creating
Analytics* - View the full discussion<http://community.qlik.com/message/533911?et=watches.email.thread#533911>

Re: Help on Max, Median Percentile

Fri, 23 May 2014 06:45:49 GMT

Hi Jacob

Thanks but I cant open it,

Re: Help on Max, Median Percentile

Fri, 23 May 2014 07:11:00 GMT

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

Fri, 23 May 2014 07:53:58 GMT

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

hic — Fri, 23 May 2014 08:46:27 GMT

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

jagannalla — Fri, 23 May 2014 09:03:42 GMT

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