In this article, we will cover how to use a custom report to calculate the average Meld completion time.
Instructions are provided to calculate the following:
Average Meld Completion Time- Overall
Average Meld Completion Time- Per Vendor
Average Meld Completion Time- Overall
Step 1:
Pull the All Melds Report. Click on the icon in the upper right-hand corner and select Reports from the drop-down menu.
Step 2:
Click on the All Melds report title. Fill in the desired Creation Start Date, Creation End Date, and select the desired property group (optional), then click Generate Report.
Note: If you’d like the report for all time, you can leave the start and end date fields blank.
Step 3:
Locate the email and download the report and open it up.
Step 4:
In excel, click on column Q (area highlighted below) to select the whole column.
Step 5:
Click on the Sort & Filter option on the Home tab, the select Filter from the drop-down. This will make a drop-down appear on column Q.
Step 6:
Click the drop-down button in column Q and uncheck the Blanks checkbox, then click OK. This will filter out any Melds that have not been completed.
Step 7:
Scroll to the last column on the right-hand side and create a new column titled Completion Time.
Step 8:
Now, you will enter the function that will subtract the values in column F (Creation Date) from those in column Q (Completion Date) to find the completion duration. Here’s how:
(Steps are displayed in this video as well: Steps a-g)
a. click in the first row underneath the Completion Time header you created
b. type an = sign
c. scroll over and click the first row in column Q
d. Type a - sign
e. scroll over and click the first row in column F
f. press enter
g. click and drag the Completion Time column to the bottom of your data set
Average Meld Completion Time- Per Vendor
Video Demonstrations:
Written Instructions:
Step 1: The Filter- first we will create a filter so all of the melds will meet our desired criteria.
1a: Click on the melds tab in the top navigational bar and click the drop-down arrow next to the word FILTER.
1b: Select the criteria that you want the Melds in this report to meet. For mine, I want the Melds to have been created last month and have a resident rating of any stars.
1c. I recommend saving this filter so you can easily pull the report any time you need updated data. You can save the filter by, scrolling down to the bottom of the filtering options pop-up box and clicking Apply Filters. Then, click Save at the top of the page, enter the filter's new name and click Save Filter.
Step 2: Calculating the Average Meld Completion Time per Vendor- now that we have made the filter we can export the report and calculate the completion time.
2a: Click Export CSV. This will automatically email a report of all Melds in your filter to the email you are signed in with.
2b: Locate the email and click Download File, then open the file.
2c: Once opened delete all columns except for "Assignee", Meld creation date", and "Meld Completion date" or copy columns "Assignee", Meld creation date" (or "Vendor accept date"), and "Meld Completion date" onto a new sheet. Then create a new column called "Completion time".
2d. Now we will need to remove the UTC from the end of the date time stamps so that excel can calculate the difference between the dates for us. Click the Find & Select option on the Home tab and select Replace.
Then, put "UTC" in the Find what: box, leave the Replace with: box blank, and click Replace All.
2e. Then, use the custom sort option to sort the Assignee column in ascending order.
2f: Insert a row between each separate vendor. Then, highlight the Meld completion time for one vendor and click the dropdown arrow next to the AutoSum option on the Home tab and select Average. This will auto-fill the empty cell with the average completion time.
2e: Repeat for each vendor.