Plexus has a range of ways to report on the information added into the platform including:
The ability to export documents and matters out of the platform provides teams with the power to further analyse and structure the data for accurate reporting.
This guide will utilise the common scenario of exporting information out of the Matters list to report on average time for a matter to move from Open to Closed, similar to the information provided in the Matters insights report. As part of this scenario, we will explore:
Whilst this guide uses a specific example for matters, each of these steps can be more generally applied to analysing the data for documents too.
Note: The Document and Matters lists contain all files in Plexus that are available to you with your account and permissions. If you are wanting to report on all matters, you will need to ensure you have permissions to view all matters.
You may also like to ensure that you do not have any filters or searches limiting the information that you can export from the platform.
Analysing the exported information for Matters
Once you have exported your Matter information out of the platform, open the resulting Microsoft Excel file.
The following column references are accurate for our default view for Matters. If you have exported a modified view of your Matters list, it is possible that the following table references will be different in your file.
Please keep this in mind when referencing the formulas in this article.
Use case: Obtaining the total duration of each matter
By scrolling across to Column H, we can create a new formula to find the difference in time between the Opened at (Created date) and Closed at dates.
Create a new column on the spreadsheet. In this example, the new column is after Column I. Name the new Column J to calculate Matter duration for each row of matters.
This formula in this example is =I2-H2.
Top tip! Head to Format and click Number to ensure that the figures show in an appropriate format.
Then double click or drag the dot in the bottom right corner of cell D2 to apply the formula down to all the relevant cells in Column D. This provides the duration of each Matter.
Use case: Negating the impact of time spent waiting for others
Some legal teams have Service Level Agreements (SLAs) related to matters. These Service Level Agreements can be impacted by time spent waiting for other internal or external stakeholders.
By exporting information related to time on the Matters list, you can further guide the data to exclude any time spent waiting for these other stakeholders.
Plexus will provide extra columns to show the time spent in each and every status.
To negate the impact of a specific status, create a new column in your spreadsheet. Then subtract the results from your totals.
To negate the impact of waiting time in this spreadsheet, the formula would be the Matter Duration, minus the two columns for waiting time. This would be represented in this specific spreadsheet as:
=J2-M2-N2
As per the last scenario, double click or drag the dot in the bottom right corner of cell D2 to apply the formula down to all the relevant cells in the new Column O. This copies the formula to complete the values for each matter.
Use case: Finding the average
To easily obtain the Average duration of each matter, in another column we can create another formula. In this example, we'll put the formula at the bottom of the results for Matter duration in Column J.
=AVERAGE(J:J) or =AVERAGE(J2:J3) which reads as "the average of the values between J2 and J3.
You can also drag and drop your mouse over the values that you would like to include in the Average, if you are unsure of the secific fields.
Using the formula above, we can find the average of all values listed in the Column.
Then press Enter.
Remember that you may need to change the data format for this field to a Number.
In this example, the Average Duration of Matters was 22.49 days.
This will reflect the Average Duration of Matters, as calculated in the Matters insights.
Top tip! By including only the Matters with a Closed at timestamp, the information calculated in the spreadsheet will be aligned with the Matters insights graph.
The matters tile will show days, hours, minutes, seconds, whereas this data will show to two decimal places.
The Matters insights shows three months worth of data by default, and you will need to take this into consideration when cross-referencing your results.
Using pivot tables for data analysis
Whilst the above information is useful, for those wanting to see this number over time:
Step 1. Click on any cell in the spreadsheet (i.e. A1)
Step 2. Click Insert at the top of the page and click PivotTable
Step 3. If you're using Microsoft Excel, you should see a pop-up similar to the pop-up shown below. Click OK.
This will take you to a new sheet with a PivotTable box on the page.
Step 4. Click inside the PivotTable box to reveal a side panel.
Step 5. In the bottom right hand corner of the screen, you'll see the option to Drag fields between areas below.
Start by clicking and dragging the field for Opened at to the heading for Rows. It should automatically create a column for your rows.
Click and drag the field for Days to close to the heading for Values. By default, this will calculate the 'Sum of days to close.'
Step 6. To change the sum to the average:
Right click any umber in the PivotTable
Click Summarise values by
Choose Average
Now you can see the Average time to close by month
Creating charts in Excel
Step 1. Click the PivotTable (or highlight the field/s you would like to chart visually).
Step 2. Click Insert at the top of the page
Step 3. Choose the Graph that you would like to create (i.e. Line graph).
For the PivotChart created above, this will create a line chart next to your data, showing a trend of this metric over time.
While we have looked at Average duration of Matters in this example, you could use the same principles to see other metrics on time.
Additionally, rather than time, you could also filter further by the assignee, organisation unit or another field relevant to you.
Need assistance? Reach out to [email protected] for more information.