Hi,
I've searched all over and can't seem to find a solution, which may mean that what I want to do can't be done or that I'm just not using the right search terms. I'm reasonably experienced with Excels functions but self taught so have probably missed important parts! Sorry for the long post.
The Background -
The team I'm working with receive enquiries into the team and then process these and send the results back out to the clients. We would like to track how many are received each day and how many are completed (with scope to also look at a number of other areas including how many days they spent waiting to be processed and who processed them). With this larger scope in mind the data report I have set up pulls out a lot of information about the enquiries. I would like to keep this as a single report that includes all the data we may want. This is to make it easier when this is passed back to them to work with as BAU (they are not excel experts!).
Currently once I have run the report and added it to excel I have a number of additional columns (* below) in the table which calculate the date the enquiry was received, if the enquiry has been completed, when this was, what the processing time was, which month and week they were received etc. I then have a second table which does a count for each day by the types of enquiries we receive. The 2nd table then goes through a pivot table so I can create a dashboard with slicers (I have a 2nd table which does something similar for the processing time and checks this against the SLA set, both are in the Data Model so a single slicer works on both)
This is fine for pulling out the data and displaying it on the dashboard but... currently the 2nd set of tables just go to the end of the year and would need to be manually added to each time we reach the end of the current period. I would like to avoid this so the team managers just need to run the single report, pop it in the data sheet and click refresh. I don't want this sheet following me though my career as I'm the only one who can update it each year.
I would like to pull the data directly from the first table and show by date the amount of enquiries received in one column and how many have been completed in the 2nd column. this way as the dates increase no background work is needed to keep the dashboard working it will just show the new dates (they would even be added to the slicer). Everything I try either ends counting everything together (if I choose one of the date columns as the columns of the pivot table or puts all the figures in the same column, If i put both date sets in the rows.
Example below of what I'm currently working with
Data from the CMS with additional columns
2nd table to calculate daily amounts
Thanks, hope you can help
I've searched all over and can't seem to find a solution, which may mean that what I want to do can't be done or that I'm just not using the right search terms. I'm reasonably experienced with Excels functions but self taught so have probably missed important parts! Sorry for the long post.
The Background -
The team I'm working with receive enquiries into the team and then process these and send the results back out to the clients. We would like to track how many are received each day and how many are completed (with scope to also look at a number of other areas including how many days they spent waiting to be processed and who processed them). With this larger scope in mind the data report I have set up pulls out a lot of information about the enquiries. I would like to keep this as a single report that includes all the data we may want. This is to make it easier when this is passed back to them to work with as BAU (they are not excel experts!).
Currently once I have run the report and added it to excel I have a number of additional columns (* below) in the table which calculate the date the enquiry was received, if the enquiry has been completed, when this was, what the processing time was, which month and week they were received etc. I then have a second table which does a count for each day by the types of enquiries we receive. The 2nd table then goes through a pivot table so I can create a dashboard with slicers (I have a 2nd table which does something similar for the processing time and checks this against the SLA set, both are in the Data Model so a single slicer works on both)
This is fine for pulling out the data and displaying it on the dashboard but... currently the 2nd set of tables just go to the end of the year and would need to be manually added to each time we reach the end of the current period. I would like to avoid this so the team managers just need to run the single report, pop it in the data sheet and click refresh. I don't want this sheet following me though my career as I'm the only one who can update it each year.
I would like to pull the data directly from the first table and show by date the amount of enquiries received in one column and how many have been completed in the 2nd column. this way as the dates increase no background work is needed to keep the dashboard working it will just show the new dates (they would even be added to the slicer). Everything I try either ends counting everything together (if I choose one of the date columns as the columns of the pivot table or puts all the figures in the same column, If i put both date sets in the rows.
Example below of what I'm currently working with
Data from the CMS with additional columns
| Enquiry No. | Date Received | Status | Status Set | Set By | Process | Date Received* | Work Completed* | Date Completed* | Completed By* |
|---|---|---|---|---|---|---|---|---|---|
| 1234 | 1/1/19 8:30 | Closed | 1/1/19 14:30 | Dave ttf | Activity 1 | 1/1/19 | Yes | 1/1/19 | Dave |
| 1235 | 2/1/19 9:35 | Closed | 2/1/19 17:30 | Sarah ttf | Activity 2 | 2/1/19 | Yes | 2/1/19 | Sarah |
| 1236 | 2/1/19 13:45 | Open | 2/1/19 13:45 | - | Activity 1 | 2/1/19 | No | Open | |
| 1237 | 1/1/19 8:30 | Closed | 2/1/19 13:55 | Dave ttf | Activity 1 | 1/1/19 | Yes | 2/1/19 | Dave |
| 1238 | 2/1/19 14:20 | Open | 2/1/19 14:20 | - | Activity 2 | 2/1/19 | No | Open |
2nd table to calculate daily amounts
| Date | Month | Process 1 | Process 2 | Amount |
|---|---|---|---|---|
| 1/1/19 | Jan 19 | Activity 1 | Activity 1 in | 2 |
| 1/1/19 | Jan 19 | Activity 1 | Activity 1 Out | 1 |
| 1/1/19 | Jan 19 | Activity 1 | Activity 1 Held | 1 |
| 1/1/19 | Jan 19 | Activity 2 | Activity 2 In | 0 |
| 1/1/19 | Jan 19 | Activity 2 | Activity 2 Out | 0 |
| 1/1/19 | Jan 19 | Activity 2 | Activity 2 Held | 0 |
| 2/1/19 | Jan 19 | Activity 1 | Activity 1 in | 1 |
| 2/1/19 | Jan 19 | Activity 1 | Activity 1 Out | 2 |
| 2/1/19 | Jan 19 | Activity 1 | Activity 1 Held | 0 |
| 2/1/19 | Jan 19 | Activity 2 | Activity 2 In | 2 |
| 2/1/19 | Jan 19 | Activity 2 | Activity 2 Out | 1 |
| 2/1/19 | Jan 19 | Activity 2 | Activity 2 Held | 1 |
Thanks, hope you can help
