Pivot table for an awkward data set

Hello,

I am a database administrator for a large institution and a new reporting request has me challenged. In our system, a new file is created, and Events are added to track actions taken by staff members. Under each Event, individual Event Activities track the steps to completing the action. One of our user groups wants to report on events in a way that encompasses the life of a new application for a clearance from submission to the time the project clears - to assess both the overall time for the process to be completed and the time between each of the various steps. The typical pathway is:

Event 1: New File Created

• Submitted
• Triaged
• Assigned (to a person or committee)
• Reviewed (by some group or another)
• Misc back and forth between reviewers and applicants

Event 2: Final Clearance

• Feedback sent
• Revisions returned
• Various additional reviews and back-and-forth
• Cleared
• Clearance letter issued

There are a number of other Events and Activities that take place after this initial phase (renewals, change requests, etc) but it is increasingly important for our end-users (applicants) to know how long they can expect the process to take, as well as helping the team that works on these files understand where bottlenecks are occurring.

Unfortunately, the system does not offer an automated reporting tool to assess the time that passes over the course of this process, however I am able to export all the data. The trouble is with the format the data is dropped into. The information is all there, but I can't seem to find a tool that will help me produce the analysis needed. A sample data set is below.

I feel like there is a way to do this as a Pivot table but I just can't seem to format it in a way that is meaningful. I hope you can help!


Event activities Jan 1 - June 22 - Sample Data.xlsx

without seeing any notion of preferred reporting it's hard to offer anything significant however, you might want to look into Power Query to simplify things - for ex.

with your table active, click on Data -> From Table / Range

this will take you into Power Query...

now select columns Application Submitted through to Triage (by clicking on table headers), then go to Transform -> Unpivot Columns

at this point, all of your headers will become rows wherever an entry value was specified, and said values will be adjacent to the respective "Attribute"

you can then, via File, choose to Load this as Connection Only - but to the Data Model

now, back in XL, add a new tab, Insert Pivot from Data Model.

you may now find you can create a Pivot using the Attribute and Value fields to do what you want?

refer attached for illustration of the above.

This has been a huge help! OK so using unpivot columns got me to the point where I could successfully format a Pivot table to display a list of activities for each file in the order they occurred. My next step that I'm trying to get to is to calculate

1. The total days elapsed for each file,
2. The average total days elapsed for all files, and
3. The average days elapsed between individual activities

In a perfect world I would like to find a way to do this within the same pivot table, but I am not sure what steps to take next, or if this is something a pivot table can do with dates. I've attached an updated sample data set where I've added a tab called Desired Endpoint with a rough idea of what I'm trying to get at (don't care about formatting at all if I can get at the right averages - just a rough idea of the calculations needed).

Event activities Jan 1 - June 22 - Sample Data w desired endrpt.xlsx

hi, refer to the attached -- more specifically, query1/sheet1...

for your average calcs, you may find it easiest to do these outside of, but referencing, the Pivot itself.