3 Replies Latest reply on Jul 26, 2018 6:40 AM by Bryce Larsen

    Call volumes and average calls dual axis chart help

    naresh.suglani.0

      Hi all I am trying to recreate the attached workbook chart in the tab "Hourly Calls" using a sample of my own data but my call data consists of various types in the "Status" dimension. I am only interested in those of status "Diverted", "Abandoned", and "Answered".

       

      The chart looks like that below, and includes a line showing the average number of calls per hour.

       

      Can anyone help me recreate this please? I had a try but found it tricking due to the call volumes split by "Status"

       

      In the attached workbook the average calls per hour seem to be calculated as the total calls per hour divided by 31, which I'm not sure why unless that is the number of days for January which is the month being displayed in the chart.

       

      Thanks in advance. Hope someone can work some magic with my sample data for me.

       

      Hourly Calls.jpg

        • 1. Re: Call volumes and average calls dual axis chart help
          Bryce Larsen

          Hi Naresh,

          You should be able to replicate this just as they've done by simply dragging Status onto the Filters pane and filter accordingly.

           

          Key items:

          • for the Circle graph, have Date on the details as to make individual circles per date (which can lead to darker shades based on frequency.
          • divided by 31: I do not see this in any fields that are used
            • you will get the average by hour for days that had a call in that hour
            • this could be off, however, as if you only have calls at 1am for 5 days of the month, it would calculate the average for those 5 days, when you probably would want to have all other 26 days in the month taken into consideration
            • to make this dynamic you'd want to calculate total days in the month on the fly, maybe something like this:

           

          DATEADD("month", 1, DATETRUNC("month", WINDOW_MIN(MIN([Date])))) -

          DATETRUNC("month", WINDOW_MIN(MIN([Date])))

           

           

          One other caveat, if you don't have data at every hour for the Statuses you've mentioned, you may need to make a new volume calculation:

          CASE [Status]

          WHEN "Diverted" THEN Volume

          WHEN "Abandoned" THEN Volume

          WHEN "Answered" THEN Volume

          ELSE 0

          END

           

          With this you can still show all hours while only showing the call volume of interest.

          Hope this helps get you going!

           

          Bryce

          1 of 1 people found this helpful
          • 2. Re: Call volumes and average calls dual axis chart help
            naresh.suglani.0

            Hi Bryce, many thanks for the reply and providing solutions to the average calculation based on the number of days of data in the month and also the new calculation for the volume.

             

            I used both in my workbook and they worked perfectly!

             

            Will do some more checks now to see if there any issues but so far so good!

            • 3. Re: Call volumes and average calls dual axis chart help
              Bryce Larsen

              Huzzah! Glad you sorted it out. I saw you asked a question earlier about using the above calculation to get the average - presume you figured that out then, too?

               

              It's a nice dashboard to replicate as well!