Date / time - functions?

[#1]

Date / time - how to calculate average time someone is signing into office - in the log we have Sign-In time & sign out time - which date fuctions will do the calculations for average time-in?

Steps are:

Seprate out Sign-IN data from Sign-Out data using colun sign-in/out columns.

Calculate avergae time in

Calculate average hours in office

Calculate % times signed in before 9am etc.

Can anyone suggest how to achive this?

Thanks,

HSD

[tresesco]

It should not be that complex. Calculate the time spent in the office simply like:

Load

          Sign_Out - Sign_In AS SpentTime  // or, use Interval() function

From <>;

Then use AVG() function to get average. Might be you have to work on the time format like 'hh' or 'mm' for hour and minute respectively with some timestamp/time/interval function.

[#3]

Thanks a ton tresseco - it was very helful to get started on QlikView!!

the problem is Sign-IN time and Sign-Out time are two differnt rows - which I need to match based on date and Employee name - currently assuming for simplycity that the record for Sign-IN time is followed by Sign-Out time -  will it need kind of calculated column - for entire excel & then bring that in QlikView or we can do this across rows calculations in here - within QlikView?

Today is mine just 2nd day of using this tool.

Thanks,

Hemant

[Gysbert_Wassenaar]

Can you post an example document with some sample data?

Please read these two documents:

• QlikCommunity Tip: How to get answers to your post?
• Preparing examples for Upload - Reduction and Data Scrambling.

[#5]

SignInOut	Date	Time	Name	Emp#	Location	Domain	WeekEnd	Remote	Status
IN	1/1/2010	9:01:10	Hemant	2	NY	BARCAP1	WEEK	Y	AccessGranted
OUT	1/2/2010	17:01:10	Shashi	10	NY	BARCAP2	WENKEND	N	AccessDenied
OTHER	1/3/2010	11:01:10	John	3	NY	BARCAP3	WEEK	N	AccessDenied
IN	1/4/2010	9:02:10	Kaka	4	NJ	BARCAP5	WENKEND	Y	AccessDenied
OUT	1/5/2010	18:01:10	Dan	1	NY	BARCAP4	WENKEND	N	AccessGranted
OTHER	1/6/2010	14:01:10	Seetha	8	NJ	BARCAP4	WEEK	Y	AccessGranted
IN	1/7/2010	9:45:10	Shila	11	TX	BARCAP3	WEEK	N	AccessGranted
OUT	1/8/2010	17:31:10	Ron	7	TX	BARCAP10	WEEK	Y	AccessGranted
OTHER	1/9/2010	17:01:10	Reena	6	TX	BARCAP6	WENKEND	N	AccessGranted
IN	1/10/2010	10:01:10	Meena	5	VA	BARCAP5	WEEK	N	AccessGranted

Here is the sample data - I need to calculate time spent in office by subtracting Time out- TimeIn for same person on same date... assuming no one is staying overnight!!

Thanks,

Hemant.

[Gysbert_Wassenaar]

Judging from your sample data people either never arrive or never check out. No person has more than one record so there's nothing to calculated from that.

[#7]

SignInOut	Date	Time	Name	Emp#	Location	Domain	WeekEnd	Remote	Status
IN	1/1/2010	9:01:10	Hemant	2	NY	BARCAP1	WEEK	N	AccessGranted
OUT	1/1/2010	18:01:10	Hemant	2	NY	BARCAP1	WENKEND	N	AccessGranted
IN	1/11/2010	7:01:10	Dan	1	VA	BARCAP9	WENKEND	N	AccessGranted
OUT	1/11/2010	23:01:10	Dan	1	VA	BARCAP1	WENKEND	N	AccessGranted

Oh. Ok I understand your point!!

Let me correct the data..

[Gysbert_Wassenaar · Accepted solution]

Ok, assuming a person is uniquely identified by Emp# and always checks in and out on the same day... you can load the sign in records and sign out records separately and join them on the Emp# + Date combination. And then calculated the time spend.

Or with the datamodel you posted you can use a set analysis expression. Create a straight chart with Emp#. Name and Date as dimensions and as expression Interval(only({<SignInOut={'OUT'}>}Time)-only({<SignInOut={'IN'}>}Time),'hh:mm:ss')

See attached qvw.

[#9]

Thanks Wassenaar!

I will not be able to see the qvw, I will try out what you have said - it should surely work if the data is real-time good data.

[#10]

I am dealing with test received and test results times and periodically, these will cross calendar days.  I've not been able to create a valid DTS concatonating my dates and times because my date format is not standard.  Unfortunately, the DTS I created is also not workable.

I have reverted back to using just the time stamp, but some results are negative (crossing calendar days)  How do you manage negative numbers when the timestamps are across more than one calendar day?

Thank you!