Count a field based on time stamp and two conditions

[#1]

Hi together,

I'm currently working on a call center dashboard and can't get my head arround some information i'd like to have. I can't even come up with some key words to search for a sulution .

I have a table showing me if an operator has logged in or out incl. the date and time for each action. 

I now want to calculate how many operator were logged in based on the selection I have made. Lets say I have seleceted the 16.03.2015, 4pm and want to know how many operator were looged in at this time. I hope someone can give me a hint on this.

Thanks and regards! 

[MK_QSL]

Provide sample data in excel format.. I have created similar kind of dashboard for employee in previous project.

[#3]

Hi Manish

unfortunately I cannot post the full QVD as it links directly into the phone system and contains a lot of confidential  data. Attached is an .xls which contains all information I would have to get the needed information.

Many thanks,

Andreas

[aveeeeeee7en]

Hi

Create a Bucket from backend using required conditions and than use it on Front end.

Below is the Snapshot:

Also, see the Attachment.

Hope, that helps you.

Regards

Av7eN

[#5]

Hi Aveeeeeee7en,

thanks for your help.

Unfortunately your solution gives me only the operator that have logged in between 4pm and 5pm. What I’m looking for is the number of operator that were logged in at  let’s say 4pm. In this case it might be that the operator has logged in at 9am and is still online at 4pm.

So somehow I need to count all logins that hasn’t been logged out at this point of the day. What I have forgot to mention is that the start time should be 3am in the morning because the phone system restarts at this time and all operator get logged off anyway.

Many thanks,

Andreas

[#6]

Is Operator Source is the login ID of the agent?

[#7]

Hi Nitesh,

Correct. Operator Source is the Extension each operator has on the phone system and could be considered as ID.

Regards

[Gysbert_Wassenaar]

The data is incomplete. In the extract below there are several logins on the same day without a logout time. There are also days with only logout times. How should these cases be handled?

341	01-01-2015	01-01-2015 13:55:54	Login To 101
341	01-01-2015	01-01-2015 22:18:56	Logout
341	02-01-2015	02-01-2015 05:59:15	Login To 101
341	02-01-2015	02-01-2015 22:01:32	Login To 101
341	03-01-2015	03-01-2015 06:26:23	Logout
341	03-01-2015	03-01-2015 18:21:33	Login To 101
341	03-01-2015	03-01-2015 19:46:00	Login To 101
341	03-01-2015	03-01-2015 20:50:40	Login To 101
341	03-01-2015	03-01-2015 22:30:49	Logout

[#9 · Accepted solution]

Hi Andreas,

Attaching the QVW along with the Excel file hope it will help.

Regards,

Nitesh

[#10]

Hi Gysbert,

sorry I have not noticed this before. After checking with the phone provider, apparently it is possible to use the login command even when the operator is still logged in. This command will also be written to the database. I also was wrong about that operator get logged off when the system restarts.

However I could check with the provider for a time when no one was logged in to have a start point. From that point onwards the first login per ID counts as “logged in” until it writes the command for “logged out”. All ‘logged in’ commands without a logged out in between, should be ignored.

Best regards,

Andreas