Simultaneous count

[#1]

Hello All,

I work in a call center and I need to calculate how many simultaneous call we receive a period of 15 mins in a day.

Anyone has any idea how I can do it or is there a fucntionality which can help me.

Thanks,

Kind Regards,

Hasvine

[effinty2112]

Hi Hasvine,

          Try this script:

ticketBASE:

Load * inline

[

idTICKET, timeSTART,     timeEND

1, 10:00:00,         10:02:00

2, 10:00:00,         10:03:00

3, 10:02:00,         10:03:00

4, 10:03:00,         10:04:00

5, 10:03:00,         10:07:00

6, 10:06:00,         10:08:00

7, 10:06:00,         10:07:00

];

tblMinTime:

LOAD Time(Min(Id)) as MinTime;

LOAD FieldValue('timeSTART', recno()) as Id

AUTOGENERATE FieldValueCount('timeSTART');

NoConcatenate

tblMaxTime:

LOAD Time(Max(Id)) as MaxTime;

LOAD FieldValue('timeEND', recno()) as Id

AUTOGENERATE FieldValueCount('timeEND');

Let vMinTime = Peek('MinTime',0,'tblMinTime');

Let vMaxTime = Peek('MaxTime',0,'tblMaxTime');

Time:

Load Time(Num('$(vMinTime)') + (IterNo() -1)/1440) as Time

AutoGenerate 1 While Num('$(vMinTime)') + (IterNo() -1)/1440 <= Num('$(vMaxTime)');

IntervalMatch:

IntervalMatch(Time) LOAD  timeSTART, timeEND Resident ticketBASE;

Now this straight table:

Time	if(count(idTICKET)>1, Count(idTICKET),0)
10:00:00	2
10:01:00	2
10:02:00	3
10:03:00	4
10:04:00	2
10:05:00	0
10:06:00	3
10:07:00	3
10:08:00	0

Kind regards

Andrew

[#3]

Hi Hasvine,

I believe this solution will work for you as all of the counts match and it is also including the times where there is a count of 0. The .qvw is also attached.

Results:

Expression in Chart Table:

=if(timeSTART<>0,count(timeSTART),0)

Load SCRIPT:

ticketBASE:
Load * inline
[
idTICKET, timeSTART, timeEND
1, 10:00:00, 10:02:00
2, 10:00:00, 10:03:00
3, 10:02:00, 10:03:00
4, 10:03:00, 10:04:00
5, 10:03:00, 10:07:00
6, 10:06:00, 10:08:00
7, 10:06:00, 10:07:00
];

timeMinMax:
LOAD Time(Min(timeSTART)) as MinTime,
Time(Max(timeEND)) as MaxTime
Resident ticketBASE;

timeBlock:
LOAD MakeTime(Num(Hour(MinTime)),0) as BeginTenMinInterval,
MakeTime(Num(Hour(MaxTime)),10) as EndTenMinInterval
Resident timeMinMax;

DROP Table timeMinMax;

Let vBeginTenMinInterval = Peek('BeginTenMinInterval',0,'timeBlock');
Let vEndTenMinInterval = Peek('EndTenMinInterval',0,'timeBlock');

DROP Table timeBlock;

AllTimeSlots:
Load Time('$(vBeginTenMinInterval)' + ((IterNo()-1)/(24*60))) as Time
AutoGenerate (1)
While Time('$(vBeginTenMinInterval)' + ((IterNo()-1)/(24*60))) <= '$(vEndTenMinInterval)';

Left Join
IntervalMatch (Time) 
LOAD timeSTART, timeEND 
Resident ticketBASE;

FinalData:
NoConcatenate
LOAD Time,
if(IsNull(timeSTART),0,timeSTART) as timeSTART,
if(IsNull(timeEND),0,timeEND) as timeEND
Resident AllTimeSlots;

DROP Table ticketBASE;
DROP Table AllTimeSlots;

EXIT Script;

Is this what you are looking for?

- Stan

[#4]

Hello Stan,

Hello Andrew,

You both provided a good solution. I tested it and it worked.

Except that I have another issue:  My ticketBASE table contain other rows (title, ticket name, date, etc, etc…). I need to have all of those row in my final table. I tried to combine INNER JOIN and INTERVAL but I didn’t work.

Any idea on how I can do that?

At the beginning, My TicketBASE table looks like this one :

idTICKET,           timeSTART,     timeEND,        date,                   categorie

1,                          10:00:00,           10:02:00         09/08/2016      hardware

2,                          10:00:00,           10:03:00         09/08/2016      software

3,                          10:02:00,           10:03:00         09/08/2016      printer

4,                          10:03:00,           10:04:00         09/08/2016      printer

5,                          10:03:00,           10:07:00         09/08/2016      hardware       

6,                          10:06:00,            10:08:00         09/08/2016      hardware

7,                          10:06:00,            10:07:00         09/08/2016      software

The other row will be use as a filter ex : only select hardware or date selection , etc…

Many Thanks,

Hasvine

[#5 · Accepted solution]

Please see the attached qvw. Added a Left Join back to the ticketBASE table to pick up the Category field.

Is this what you need?

[#6]

Hi Stan,

It works great. Thank for your help.

Thanks everyone for the contribution.

Kind Regards,

Hasvine