Weekly reporting - formula check

[simransonu08]

Looking for your expertise advise.

Attached are the reporting documents that I maintain for my team (call center), to send out weekly/monthly stats. The application used at my company does not really provide an accurate average report. So I manually dump data into Excel to calculate the average. We don't assign metrics to agents but I like to share data with them.

If you can take a look at the July WK4 stats to see if my formula's are right and if the numbers showing correct average.
Please no rude comments. I am not a expert in excel. I am just trying to do my job and help out my team to improve the service.

Report 7.17-7.21 and 7.24-7.29 is the raw data downloaded from our application.

July WK4TEST is in Excel.

[dflak]

I am willing to give this a shot.

I'll need more information. What columns do you pull from the CSV files and how do they relate to the report? Are you just reading data for a single week or do you use both weeks? If you use both weeks, how do you do the calculations?

[simransonu08]

Thank you very kindly.

I've attached a new document with the highlighted areas that I mainly focus on to get the average of the agents and the average of the overall helpdesk.

I send weekly and monthly stats to the team so they can see their overall individual performance and helpdesk.

I include the previous two weeks' data when I send the weekly report for their comparison.

What I usually do is, dump my pivot table numbers into the July-Wk4 Data report, which calculates the average of all and shows agent numbers. 

ATT - Average talk time
ACW - After call work
AHT - Average handle time




Below are the following stats:

Agent number of calls handled vs. CSC Helpdesk
Agent average talk time vs. CSC average talk time
Agent average after-call work  vs. CSC average after-call work
Agent average handle time (talk+hold+ACW/calls = AHT) vs. CSC AHT

[dflak]

OK, so the objective is to produce a report with two tabs: Last week and this week. Then you want a current week for selected agents and all others.

This is my plan of attack. Make a "base" directory for your report. Make two subfolders called data and archive. Download the CSV file with whatever application produces it and save it in the data directory with the name Data.csv (or whatever). Always download it with the same name and overwrite what was downloaded in the previous week. Part of the import data process will be to make an archive copy and put a date stamp on it.

You will do the download manually. I will give you a button to do the rest of the work. I will also give you a table to refresh which agents you want to look at.

Does this sound like what you would like to do?

Also I need some confirmation.

The 4 items under each agent's name come from column H in the data (Skill)
Agent Name is Column L
Skill Name is column H
Agent Time is column U (Agent Minutes)
ACW minutes is Column X
Hold time is Column AE

Let me know if I have these correct or if I have to add something else.

All the rest of the information comes from these columns and is based on formulas as you indicated above.

[simransonu08]

I appreciate your time and your great plan for me. You are my hero!
I've attached the Excel file this time as the CVS file was not saving my highlights and pivottable. In the first sheet Report 7.24_7.28, the highlighted yellow color columns were manually inserted to convert it from seconds to minutes. For example, Agent_mins is a result of Agent_time.
ACW_mins is a result of ACW_Time and Hold_Time is a result of Hold_mins.

[dflak]

I am still not finished with this yet, but here is an intermediate report.

Things will run from the Control Panel. This is where I will place the button to import data and make the report.

This is also where you will configure the report if necessary. The Table in Columns D:F shows what column I take from the raw data and in which column I place it on the data sheet in the application. Please check this, I think I may be looking at the wrong column for hold time. Fixing this will be easy: just change the table.

I also use the Control Panel to do scratch calculations. There is a pivot table on the pivot sheet. This gets agents whose names are not N/A and have at least one skill that stars with CSC. The program goes down each name on the list and it fills it in in cell K2, the rest of the cells in columns K:W are calculated using the downloaded data and then copied to the This Week Sheet.

For the purposes of testing, I have my values on top and what you are reporting under it on the Control Panel Sheet. They match except for hold time which I may be downloading from the wrong column or need to convert days to hours or vice versa.

I renamed your Data Sheet to Summary A because I wanted to call the imported data, Data. I am big into naming conventions and this will help me out.

What you originally called Data will now be called This Week. I will also copy this report to the Last Week sheet when you run the report next time. I do not have that programmed, yet.

I will most likely use the This Week / Last Week tables to generate the weekly comparisons for the individual agents.


I have some more questions:

Do you want to see agent N/A?

Column N contains the name of the agent. I'm OK with that.

Columns O and P contain created and closed. Where do these numbers come from?

I need some help with Columns R to AB. On the Control Panel Sheet, I have what I think these figures should be based on the data (Light Blue) and what you were reporting (Light green) - we disagree on Hold Time. I might be downloading the wrong column.

I still have some things to program. These are pretty much standard coding, I've done this kind of thing before. I am proceeding slowly and taking it one step at a time.

I ask that you compare my results with yours and tell me if you see any differences.

I know that I need to so some cleanup and formatting (like getting dates on the report).

I will most likely remove the individual sheets and regenerate them with each report. So if a person is NOT currently in the pivot table, they will not have a sheet for that week. Tab names will be the full agent names alphabetically by first name. I can work around this if you want something different.

That's it for now. I hope to have something for you to evaluate early next week. I'll even give you instructions on how to run the report for the previous week so you'll have two weeks from the start.

[dflak]

P.S. It would not be difficult for me to set it up to mail individual reports to the agents. All you would have to do is maintain an email list of agent names and email addresses. I have a module in my "toolbox" that makes sending mail easy. We can discuss this when we get finished with phase I.

It's sunny, 82 degrees and I am off to take a dip in my pool.

[simransonu08]

In the summary A sheet, I get columns A, B, C, D, and E from the raw data report that I attached in the previous reply "Report 7.24_7.28". 

The column O and P data is from our ticketing (CRM) system, where agents create cases.

I get the agent hold data from the raw report "Report 7.24_7.28" in the AF column.

I am a bit confused with the control panel data and not sure if I am looking at it right.

Did you get those numbers from the raw data file that I attached in the previous message? The report name is "Report 7.24_7.28". 

I am not so bright when it comes to Excel and calculations. I usually follow the formats that were used by my previous company; my current company does not have a proper reporting system, so I was trying to figure out what the best way was to share this report with my team.
If you'd like, I can attach a fresh MTD report for last month so you can look at the raw data.

Pool sounds really good! I hope you enjoy your pool time.

[dflak]

Are you telling me that columns O and P (open and closed cases) are entered manually?

Is there a download that has this information in it? We can also import that information and fill it in automatically. Otherwise I will give you a table where you can enter the data and it will be inserted into the appropriate places in the report.

I am almost finished. I will provide complete documentation on how to run it (click two buttons) and explain what is on the control panel sheet. You probably won't need to change any of it. This sheet is used mostly to configure the report and do scratch calculations. You may also be able to use it for debugging.

Get me the information for the open and closed cases and I should be able to complete the project.

One more question: I assume you run the report on Monday for the week ending the prior Saturday. Is this correct?

[simransonu08]

Yes, there is another report for open and closed cases run through a different application, Service Cloud.
Attached are the sample reports showing the closed and created cases reported by my team.
As I mentioned in the previous response, I have attached the month-to-date agent stats report for this month.

This will give you the previous two weeks' data if you want to reference that for your reporting. 

[simransonu08]

I am unable to attach the other two files to this thread it is exceeding the upload limit.
if there is any other way I can forward you those two sample reports.

[dflak]

Let me catch up to you. I have the normal report and I got the case creation report. I will roll the latter into the programming.

I will then ask you for the last two week's worth of data and the latest case creation and I will run a sample report that you can QA. If you like what you see, I'll clean things up, if necessary and also provide you with documentation.

[dflak]

I have a question about open and closed statuses. Status closed is definitely closed. If the status is NOT specifically closed, then is the case considered to be still open. Also do we want to track New?

[dflak]

I've reprogrammed the application to take in the cases created and closed. I am confident that I am importing the cases data properly, but the numbers I calculate do not seem to be in the ballpark with what you reported.

Today is Monday. You have data for the week ending 8/26 for the CSV files and the XLS file. Give me this data and also the two files for the previous week, and I will generate this week's report. You can compare it with what you think it should be. If we match I will clean up and document the application.

The information on the attached report is old and the two week's data are the same because I only used on week's worth of data.

[simransonu08]

I am sorry for the late response. I work for the school system, so I have been busy at work with high call volume and case overload. 
I've attached the MTD August report that will give you overall month and week data if you add filters by date in the pivot table. 

[JeteMc]

Post #15 indicates that a new file has been added, however there is no file attached to post #15.

[simransonu08]

I am unable to attach the MTD report here. It says the upload failed.
not sure why.

I can either email you the file or share it through Google Drive. Please let me know how I can send it. 

[AliGW]

Try zipping it.

[dflak]

Gee, Ali, I'm going to have to get up earlier if I am going to beat you to the post! .

What Ali said. The file may be too big (something that looks like we can fix). Zip files compress and I think they have a bigger download limit.

[AliGW]

I'm in the UK - it was gone 2pm when I posted that!!!

[simransonu08]

Awesome! Thank you both.
I've uploaded the call data for last month.

[simransonu08]

Good afternoon,

Please do let me know if you can see the last upload.
I hope you have a great week!!!

[dflak]

This is only half the data I need. I need the CSV file and the XLS file that I mentioned above.

[simransonu08]

I am sorry if I missed out on the full message.
I just uploaded new documents with CVS and XLS files.
The CVS file shows the call data.
XLS files show Case creation and case-closed data

[dflak]

I got some very weird results.

t appears that the format of the input data has changed

My original "mapping" was
- Column L was the Agent Name
- Column U was agent minutes
- Column X was ACW minutes
- Column AF was hold time
- Column H was Skill Name

According to what I am seeing now.
- Column L is still agent name
- Column U is Post Queue
- Column X is Abandon Time
- Column AF is Disp Comments
- Column H is still Skill Name

I think the new mapping should be:
- Column L Agent Name
- Column T agent minutes
- Column V ACW minutes
- Column AC hold time
- Column H Skill Name

Please confirm that these are the correct columns for these items. Also confirm that they won't change position again.

[simransonu08]

I am not sure why the formatting changed as its a Pre-built report by the vendor.
I've reviewed the report and confirmed that you mapping should be:

- Column L Agent Name
- Column T agent minutes
- Column V ACW minutes
- Column AC hold time
- Column H Skill Name

[dflak]

Thanks for the confirmation.

Since I only had one set of data to work with, I ran it twice pretending to be WE 9/16 and WE 9/23.

Some of these figures look grossly inflated: hundreds of thousands of minutes. I've confirmed some of them with the raw data. Either the data are wrong or the logic is wrong.

Check over this report to see if it makes sense to you. If the figures don't look right, then let me know which ones they are and we can figure out what's happening.

[simransonu08]

I have updated my response and question in the attached document.
Kindly please review it and let me know.

[dflak]

I am confirming that I received this message.

Question 0: The directory structure looks to be correct.

Question 1: You can name this file anything you want.

Question 2: I need to refresh myself with these files, but the names do appear to be correct. Data.csv should contain the call information. Cases.xls should contain the information on open and closed cases. You indicate that this information is contained in two separate files. I must have missed that. This would take some tweaking. It should not interfere with the running of the program, but you'll be missing either open or closed cases. I would like to get some clarification on this point since I seemed to have missed something.

Question 3: The highlighted error message indicates shouldn't really happen. What this statement is doing is checking for the existence of the file in the specific directory. If it does not exist, it should pop up a message on the screen to warn you (that's the next line in the code). So, I am at a loss, at least for the moment as to why this is happening. One thing that might help me debug it is to have you run the program again. Then, when you get the error message, hover the cursor over PathName and then FileName and record those results. This may give me information as to which file is misbehaving.

The program is designed to process only one CSV and XLS file at a time.

So if you want to "catch up." Set the override date on the control panel to 9/16 then download the data for this week in the Data Folder. Name them Data.csv and Cases.xls. Then download the data and copy the tables using the buttons on the Control Panel sheet. When you download the data, the program will copy each file to the archive folder and it will put a date stamp on it automatically. There is no intervention needed on your part.

Next, remove the override data on the control panel. It should calculate last Saturday's date. If you get to it too late, then use the override date again. Copy in the data to the datafile and overwrite the old files that are there. Repeat the download and table copy functions and you should be up to date.

As for the numbers. Save a copy of the report I sent you. Then I ask you to pick an agent for whom you know what the data should look like. Then we can look at the data and go through it manually to see where the inflated numbers come from. We might have to look at a couple of agents.

Do the test to get the error and let me know what PathName and FileName values come up when you hover over them - there might also be an error message that comes up when you hover over them. I'll need to know that if it happens.

Also explain to me what the situation of the case files is.

[simransonu08]

Please see the attached zip file, and please review the Weekly Report 09.29 Response first.
I was able to run the file this time, and I have listed all the details of what I did.
I would still need your assistance for the report to show cases created and closed by the data in the report. 

I appreciate your help.

[dflak]

I can see that there is one XLS file that is used to get cases closed and another XLS file for cases created. You will have to download three files (one raw data - CSV. one case closed - XLS and one case created - CSV)

I will have to reprogram the file to accommodate the two downloads.

Each record in the open file is considered created even if the status is closed. Each record in the closed file is closed.

[dflak]

I think I've got it. Read the documentation. The file names to download are changed. I ran the program with the data files you provided. You should be ready to run it for the week ending 9/30.

I did not do any testing on this. Let me know if anything else needs to be corrected.

[simransonu08]

I downloaded the xlsb file and followed the directions from the weekly report documentation.

I saved the files as you indicated, but the xlsb file does not seem to be calculating the closed-case data correctly.

I see it shows the results only to Aaliya Syed (that's me) and Shannon Hembrey; for the rest of the agents, it shows a 0 count. 

When I go to the cases sheet, I can see all agent names are showing up on that sheet, but it does not show the count on the actual agent sheet. 
This the actual count of number of cases created and closed for last week.

MTD 09/25/2023 - 09/29/2023
"SC Case
Created by" Created Closed

Andrea Everhart 0 2

Giada Robinson 69 83

Hallam Guiler 4 26

Sean Eber 106 111

Shannon Hembrey 85 90

Willona Torres 69 74

Aaliya 6 31

Ronnie Bell 0 0

N/A 0 0




Grand Total 339 417


I've uploaded the files that I downloaded and ran for your reference.

[dflak]

OK, I will look at this. Thanks.

[dflak]

I see the problem. The Cases Closed data contains the person's Middle Initials. None of the other data include this. I will have to compensate for this.

[simransonu08]

I would like to show you one more thing, if you don't mind.
When I ran the report, I saw that the CSC grand total was not updated accurately.
If you see the attached screenshot, this is the first agent, "Aaliya Syed." It does not show an accurate grand total in this week or the previous week file.
I am not sure from which sheet this is copying the data; I could not find a way to fix the error. 
If you could direct me, I can see if I can correct that. 

[simransonu08]

I hope you are doing well.

I was just checking back on this thread to see if you needed any additional information from me.

[dflak]

My apologies for not getting back to you sooner.

As for the previous week's information for each person. I simply copy what used to be the current week to the rows below it.

The calculations are made on the Control Panel Sheet. As I bring up each agent in the code, I calculate the individual metrics in columns P through AB and then copy these to the individual's sheet.

The Totals are calculated in columns AD through AN. Column AE has the formula =COUNTIFS(Table_Data[Skill Name],$AD3) coped down for the fixed Skill names.

The individual calculation differs in that it accounts for the agent's name: =COUNTIFS(Table_Data[Agent Name],$P$2,Table_Data[Skill Name],$P3)

I hope this helps.