Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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?
Hi Stan,
It works great. Thank for your help.
Thanks everyone for the contribution.
Kind Regards,
Hasvine