topic Re: Interval calculation in QlikView

Interval calculation

takama13 — Tue, 24 Jun 2014 10:36:33 GMT

Hi,

I'm a fresh new user of Qlikview.

I have an excel database organized as per below :

Entity - Date of Arrival - Start Working time per gang - End Working time per gang - Date of Departure

Which leads to this king of data :

A - 24/06/2014 08:00 - 24/06/2014 08:12 - 24/06/2014 17:00 - 24/06/2014 20:00

A - 24/06/2014 08:00 - 24/06/2014 08:03 - 24/06/2014 12:17 - 24/06/2014 20:00

A - 24/06/2014 08:00 - 24/06/2014 13:17 - 24/06/2014 18:15 - 24/06/2014 20:00

B - 20/06/2014 23:00 - 21/06/2014 01:12 - 21/06/2014 05:12 - 21/06/2014 08:00

B - 20/06/2014 23:00 - 20/06/2014 23:15 - 21/06/2014 07:55 - 21/06/2014 08:00

So fear each entity, Date of Arrival and Date of Departure are the same. What is changing for each entity is the Start and End of Working time per gang. I would like to calculate the waiting time for each entity before and after work times.

Therefore for entity A, I need to calculate :

Waiting time before start of work = "earliest Start Working time for entity A" - 'Date of Arrival" = "24/06/2014 08:03" - "24/06/2014 08:00" = 3 minutes

Waiting time after end of work = "Date of Departure" - "latest End Working time for entity A" = "24/06/2014 20:00" - "24/06/2014 18:15" = 1 hour and 45 minutes

How can I do that with Qlikview ?

Thanks

Re: Interval calculation

Gysbert_Wassenaar — Tue, 24 Jun 2014 10:40:09 GMT

Use entity as dimension and two expressions:

interval(min([Start Working time per gang]) - [Date of Arrival], 'hh:mm:ss')

interval([Date of Departure] - max([Start Working time per gang]), 'hh:mm:ss')

If your datetimes are string values then first make numeric timestamps from them in the script:

LOAD

Entity,

timestamp#([Start Working time per gang],'DD/MM/YYYY hh:mm') as [Start Working time per gang],

...etc

FROM ...mysource...;

Re: Interval calculation

takama13 — Tue, 24 Jun 2014 12:33:58 GMT

Thank you for your quick answer, it's working.

I missed to mention that I have several occurrences for each Entity and now I want to average them :

Entity - Occurence - Date of Arrival - Start Working time per gang - End Working time per gang - Date of Departure

A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 08:12 - 24/06/2014 17:00 - 24/06/2014 20:00

A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 08:03 - 24/06/2014 12:17 - 24/06/2014 20:00

A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 13:17 - 24/06/2014 18:15 - 24/06/2014 20:00

A - Occurence 2 - 15/06/2014 12:00 - 15/06/2014 12:05 - 15/06/2014 15:05 - 15/06/2014 18:00

A - Occurence 2 - 15/06/2014 12:00 - 15/06/2014 13:00 - 15/06/2014 14:00 - 15/06/2014 18:00

B - etc

So Waiting time before work for Entity A - Occurence 1 = 3 minutes

Waiting time before work for Entity A - Occurence 2 = 5 minutes

I want to show Average time before work for Entity A = 4 minutes

I set Entity as dimensions and used :

avg(interval(min([Start Working time per gang]) - [Date of Arrival], 'hh:mm:ss'))

But I get no result.

Re: Interval calculation

Gysbert_Wassenaar — Tue, 24 Jun 2014 12:41:25 GMT

If you nest aggregation functions, like avg and min, then you need to use the aggr function. Try

avg(aggr(interval(min([Start Working time per gang]) - [Date of Arrival], 'hh:mm:ss'),Entity,Occurence))

Re: Interval calculation

takama13 — Tue, 24 Jun 2014 12:53:37 GMT

I understant the concept.

I now have a result but the value returned is... a value.

How can I show it as a time ?

EDIT : this time could be over 24. So I would like to be able to show 36 hours for example.

Re: Interval calculation

Gysbert_Wassenaar — Tue, 24 Jun 2014 12:57:45 GMT

You can move the interval function in the expression to around the avg function instead of around the min function where it is now.

Re: Interval calculation

takama13 — Tue, 24 Jun 2014 13:05:50 GMT

Working perfectly, thank you very much !