I have raw data that I get from our phone system. I would like to be able to display it to show the number of inbound calls we get each week. I think the correct way to do this is with a Pivot Table, but it doesn’t have to be done that way. The resulting Excel file will be one where I can use the “Change Data Source” and point it to any of the output files from our phone system and have it generate the table and chart shown as a sample screenshot attached. It does not have to be EXACTLY like my example, but this is the information I want to show.
NOTES ABOUT THE DESIRED EXCEL TABLE AND CHART (see [url removed, login to view]):
-The parts highlighted in Red must be included, I don’t care if the other columns are there or not.
-The data source may be one day or it may be a few years worth of data, the Excel table/chart that you build must be able to react properly to any quantity of data. You can set a limit of 3 years if that helps.
-For this purpose of this, weeks ENDS on Sunday. I have looked at the WEEKNUM function in Excel and this is possible.
-Even if a week has less than a full week’s of days, it should still be shown.
-Please use the week ending date on the chart’s X axis as I have shown in the screen shot (not week numbers).
-Weeks should be sorted Oldest to Newest (top to bottom in the table) and (Left to Right in the chart).
-Please include a trend line (shown in light blue on my sample).
NOTES ABOUT THE DATA FILES:
-FORMAT: They come from the phone system as CSV files. I would prefer to use them as CSV, but it doesn’t seem that Pivot Tables can use CSVs as a data source. If you can make it work with CSVs that is great, but if not, you can assume that I will open the CSV and save it as an XLSX. I will not make any other changes to the source data.
-FILTER: Only include records/rows that have they “Type” (column C) is equal to “Inbound Call”. If they are blank or say something else, do NOT include that row.
-FILTER: You should only include records/rows were the “Minutes” (column B) is greater than “1” (do not include 1).
-SAMPLES: I have attached 3 sample data files. One with 3 days of data, one with 2 months of data and one with 12 months of data. I have left these in the CSV format.