topic Re: time interval in QlikView

time interval

Tue, 29 Jan 2013 16:16:57 GMT

Hi all,

I have a problem that is starting to look a little impossible 😞

I have a table box that shows a list of activities by time :

First name	Last Name	Time	Date	Activity
John	Doe	08:57:27	21/01/2013	Arrived at Work
John	Doe	08:58:57	21/01/2013	Did something 0
John	Doe	08:59:24	21/01/2013	Did something 1
John	Doe	09:01:19	21/01/2013	Did something 2
John	Doe	09:05:31	21/01/2013	Lunch Start
John	Doe	09:49:41	21/01/2013	Did something 4
John	Doe	09:53:44	21/01/2013	Lunch Finish
John	Doe	10:43:45	21/01/2013	Did something 6
John	Doe	10:48:23	21/01/2013	Did something 7
John	Doe	12:23:40	21/01/2013	Did something 8
John	Doe	12:27:07	21/01/2013	Went Home

I need to find how long the person was on lunch.

The interval between Activity 'Lunch Start' and 'Lunch Finish'

It would be great if i could display the time spent on lunch in a seperate text box.

Any Ideas ??

Thanks

Martin

Re: time interval

Nicole-Smith — Tue, 29 Jan 2013 16:42:49 GMT

Check attached example.

Re: time interval

swuehl — Tue, 29 Jan 2013 16:45:25 GMT

You want just a text box for John?

Maybe like this:

='Lunch time John spent: ' & interval(sum(aggr(

only({<Activity= {'Lunch Finish'}, [Last Name] = {Doe},[First Name]= {John}>} Time) -

only({<Activity= {'Lunch Start'}, [Last Name] = {Doe},[First Name]= {John}>} Time)

, Date)))

which takes care for multiple lunch breaks on multiple Dates that needs to be summed. (Or just

='Lunch time John spent: ' & interval(

only({<Activity= {'Lunch Finish'}, [Last Name] = {Doe},[First Name]= {John}>} Time) -

only({<Activity= {'Lunch Start'}, [Last Name] = {Doe},[First Name]= {John}>} Time)

)

if you don't need to take care of that).

Re: time interval

jagan — Tue, 29 Jan 2013 16:56:35 GMT

Hi,

Try this

=TimeStamp(Timestamp#(max({<Activity={'Lunch Finish'}>} Date) & ' ' &max({<Activity={'Lunch Finish'}>} Time), 'dd/MM/yyyy hh:mm:ss') -

Timestamp#(max({<Activity={'Lunch Start'}>} Date) & ' ' &max({<Activity={'Lunch Start'}>} Time), 'dd/MM/yyyy hh:mm:ss'), 'hh:mm')

Hope this helps you

Regards,

Jagan.

Re: time interval

Mon, 04 Feb 2013 12:15:47 GMT

Hi,

Thanks for your reply !

It works great appart from when the data has multiple lunch times :

First name	Last Name	Time	Date	Activity
John	Doe	08:57:27	21/01/2013	Arrived at Work
John	Doe	08:58:57	21/01/2013	Did something 0
John	Doe	08:59:24	21/01/2013	Did something 1
John	Doe	09:01:19	21/01/2013	Did something 2
John	Doe	09:05:31	21/01/2013	Lunch Start
John	Doe	09:49:41	21/01/2013	Did something 4
John	Doe	09:53:44	21/01/2013	Lunch Finish
John	Doe	10:43:45	21/01/2013	Did something 6
John	Doe	10:44:00	21/01/2013	Lunch Start
John	Doe	10:48:23	21/01/2013	Did something 7
John	Doe	12:00:00	21/01/2013	Lunch Finish
John	Doe	12:23:40	21/01/2013	Did something 8
John	Doe	12:27:07	21/01/2013	Went Home

Then i just get 00:00:00

If you have any ideas on how to fix this it would be fantastic !!

Thanks for all your help 🙂

Martin

Re: time interval

swuehl — Mon, 04 Feb 2013 12:55:17 GMT

That's because there is no unambiguous answer for the lunch timestamps when evaluating the suggested only() expression, looking at that given date.

Maybe you can calculate the lunch time intervals already in the script, please check attached sample.