Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

14 Replies
Not applicable
Author

Hi Jacob

Thanks but I cant open it,

Not applicable
Author

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?


Not applicable
Author

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

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

jagannalla
Partner
Partner

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