Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can move the interval function in the expression to around the avg function instead of around the min function where it is now.
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...;
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.
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))
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.
You can move the interval function in the expression to around the avg function instead of around the min function where it is now.
Working perfectly, thank you very much !