14 Replies Latest reply: Sep 16, 2016 6:05 AM by Hasvine Dhurmea RSS

    Simultaneous count

    Hasvine Dhurmea

      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

        • Re: Simultaneous count
          Elena Isupova

          Hi Hasvine,

          I am not sure your question is clear for me. I think you can divide your whole day with the 15 minutes intervals and then using Set Analysis make counts on calls. If you have the call's start time and end time stored, then you can simply check whether definite 15 minutes timestamp belongs to that interval.

           

          I mean:

          =Count({<=(TimeStamp(Today))={'>=StartTime<=EndTime'}>} CallID)

           

          Or smth like that. Maybe it helps you.

           

          Elena

            • Re: Simultaneous count
              Stan Bennett

              In the below example, the Call Details table is showing one row per phone call. The first column is the time of the phone call and the second column is what bucket you want to assign to it.

              In the Call Summary table the first column is the Bucket, which exists for each 15 minute interval and the next column is the count of calls assigned to the corresponding bucket.

               

              If the above is what you need then take a look at the following script:


              CallTimeStamp:
              LOAD *,
              Time(Class(TimeStamp#(calltimestamp,'M/D/YYYY h:mm:ss TT'),MakeTime(0,15))) as FifteenMinInterval,

              Inline [
              calltimestamp
              8/29/2016 8:14:03 AM
              8/29/2016 8:22:56 AM
              8/29/2016 8:26:26 AM
              8/29/2016 8:28:06 AM
              8/29/2016 8:35:27 AM
              8/29/2016 8:40:28 AM
              8/29/2016 8:46:59 AM
              8/29/2016 8:53:09 AM
              8/29/2016 8:56:29 AM
              8/29/2016 8:56:48 AM
              8/29/2016 9:01:39 AM
              8/29/2016 9:08:19 AM
              8/29/2016 9:15:38 AM
              8/29/2016 9:19:15 AM
              8/29/2016 9:28:19 AM
              8/29/2016 9:42:35 AM
              8/29/2016 9:43:06 AM
              8/29/2016 9:46:49 AM
              8/29/2016 9:47:39 AM
              8/29/2016 9:48:52 AM
              8/29/2016 9:52:52 AM
              8/29/2016 9:56:23 AM
              ]
              ;

              Exit Script;

               

              The key here is to use the Class function, which will create the 'buckets' that you need for the 15 minute intervals.

               

              Hope this is helpful,

               

              -Stan

            • Re: Simultaneous count
              kushal chawda

              Please share some sample data

              • Re: Simultaneous count
                Anil Babu Samineni

                I also working for that project.

                 

                That scenario i do have Pre Call , Post Call and In Call.

                 

                Can you please elaborate more

                • Re: Simultaneous count
                  André Gomes

                  Hi,

                   

                  have you seen this?

                   

                  Service Request Call Center KPIs | Qlik Community

                   

                  HTH

                   

                  André Gomes

                  • Re: Simultaneous count
                    Andrew Walker

                    Hi Hasvine,

                                        If you have a timestamp for the start and end of each call you could use an Interval Match on the minutes or seconds.Then for each minute or second you can obtain the number of calls that were in progress at that point.

                     

                    good luck

                     

                    Andrew

                    • Re: Simultaneous count
                      Toni Kautto

                      As mentioned above you need to share sample data in order to allow providing you with qualified help. The structure of the data decides what solution options you will have. You do not have to provide your actual business data as this can be sensitive, so consider providing a excel sheet or text file with some sample data to give us an idea of your data structure.

                      • Re: Simultaneous count
                        Hasvine Dhurmea

                        Hello All,

                         

                        My apologies for the late reply. I was out of office.

                         

                        Here is an example and the expected result.

                         

                        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

                        ];

                         

                        We need to calculate the simultaneous tickets for each timestamp and the result should be something like this

                         

                        time simultaneous tickets (count idTICKET where timeSTART<= time <= timeEND)

                         

                        Expected Result:

                        Time               Number of simultaneous ticket

                        10:00:00           2

                        10:01:00           2

                        10:02:00           3

                        10:03:00           4

                        10:04:00           2

                        10:05:00           1

                        10:06:00           3

                        10:07:00           3

                        10:08:00           1

                        10:09:00           0

                        10:10:00           0

                         

                        Thanks for your help.

                         

                        Kind Regards,

                         

                        Hasvine

                          • Re: Simultaneous count
                            Stan Bennett

                            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

                              • Re: Simultaneous count
                                Hasvine Dhurmea

                                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

                            • Re: Simultaneous count
                              Andrew Walker

                              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:002
                              10:01:002
                              10:02:003
                              10:03:004
                              10:04:002
                              10:05:000
                              10:06:003
                              10:07:003
                              10:08:000

                               

                              Kind regards

                               

                              Andrew