2 Replies Latest reply on Jan 3, 2019 2:40 PM by Kevin Esther

    Count the number of calls by area transferred and received

    Kevin Esther

      Hi,

       

      I have been at this most of the day not getting very far.

       

      A sample workbook is attached of call data between different areas.

       

      The data is got a unique call ID showing it's journey between areas.

       

      I need to return a count of call transferred and received by each area similar to the below table.

       

       

      transferred from >>>>>>> Received by >>>>>Area 1
      Area 2
      Area 3Area 4Area 5
      Area 110015100
      Area 20156210
      Area 3500155010
      Area 45060501050
      Area 510505010010

       

       

      In the data I have counts that tell me the size of the partition and count of call per call ID.

       

      any help would be appreciated.

       

      Kev 

        • 1. Re: Count the number of calls by area transferred and received
          Sarah Bartlett

          Hi Kev,

           

          Looking at your data, area is only shown once for every call ID and there is no distinction between transferred from or received by.

           

          If we had the data to show the area a call from transferred from (i.e. Area 1) and received by (i.e. Area 2) we would be able to show the data in a table like you have above. However, since area only appears once against each unique call ID at the moment we are unable to do this. Do you have this data available?

           

          I hope this helps.

          • 2. Re: Count the number of calls by area transferred and received
            Kevin Esther

            Hey Sarah,

             

            Thanks for getting back to me.

             

            Bit background on the raw data I used python to do data mugging it joins various sources create a row by row activity of the call id.

             

            I order the data by call start time then groupby the call ID to get the counts then join back together again.

             

            The call count shows the total number of calls and the cumulative count is the order of the calls for each ID.

             

            For example the first call ID 1108 started at Area 2 was then transfer to Area 2 again (internal transfer) then it was resolved in Area 4.

             

            So where you see a cumulative count of 1 this is the first call that been transferred, these counts can range from 2 or more and to the same area.

             

            In my head am thinking that I need to count strings for example Area2>Area2 then Area2>Area4.

             

            I give it ago at using LOD calculations however struggling to get it to work.

             

            {fixed [Call Id]: MAX(IIF([Cumulative Count]=[Call Count],[Area], NULL))}

             

            This works however only for calls that have a count of 2.

             

            The index is also in order so am not sure if I can this with LOD and the call ID.

             

            Kev