OBJECTIVE: This article provides instructions on how to run a Top Offenders (the employees who have the most exceptions in visit verification) report in AlayaCare.
You must have access to the Project Keystone SharePoint to complete this process.
Table of Contents:
Downloading the Top Offenders Report
1. Navigate to the GA AutoConfirmed and Exceptions Workbook.
2. Locate the most recent version by clicking the three-dot Menu icon to the right of the file and then clicking Copy to.
3. Click Copy Here from the pop-up window. This allows you to make a new version of the file and rename it to the applicable date.
4. Once the file is copied, locate the file and click the Menu icon. Select Rename. Update the date on the report to reflect the date that the report was created.
5. Once the file is renamed, click the Menu icon and select Open. Click Open in App. This will open the file in Microsoft Excel on your desktop.
6. From the Excel spreadsheet, navigate to the HAHUS tab from the bottom left of the spreadsheet. This tab contains all of the visits that the report requires.
7. Clear the contents from Columns C thru R and Row 9 by selecting the data, right-clicking, and selecting Clear. The report should be empty.
8. Download the necessary data from AlayaCare. Navigate to the Dashboard module in AlayaCare and click Operations Reports.
9. Click the Georgia folder and locate the current day's date from the file list. Click the applicable file. The file will automatically download.
10. Open the file from your computer. Copy Columns A thru P starting from Row 2.
11. Paste the data into Column C, Row 9 of the original Excel file. This will transfer the data collected from the Operations report.
12. Once the data has been pasted on the report, right-click on the HAHUS Sheet tab and rename the date to the current date.
13. Navigate to the AL Exception Pivots Sheet and right-click the Client Cost Center > Employees > Exception Code Pivot table and select Refresh. The new data is now populated in the Report.
14. Update the Week filter to display the most recent week by clicking on the Filter icon next to Visit Week. Check the most recent week's check box and click OK.
15. Save the document once the table is up to date. This will automatically save the document to SharePoint.
Running the Top Offenders Report
1. Navigate to the GA Exception Pivots sheet within the workbook. Scroll to the right to view the Client Cost Centre > Employee > Exception Code section.
2. Click the Visit Week filter icon and select Filter.
3. Edit the filters to show the most recent week only. Click OK.
4. Click anywhere in the pivot table to view the Menu and fields on the right. Select the Service Code field and drag it to Filters. Service Code will appear at the top section of the report underneath BRANCH Georgia. Click the Service Code drop down menu.
5. Click the + icon from the Filter menu to open the list of service codes. Uncheck the check box for the following service codes so they are not selected:
- Admissions Visit
- Annual Visit
- LPN Hourly
- RN Hourly
- Structured Family Care
- Supervisory Visit
- Visit Med Mgmt LPN
6. Click the + icon next to each office to view all of the employees listed.
7. Once all branches have been drilled down, copy the data all the way to the bottom including the following columns:
- Cost Centre Name
- Employee Name
- Visit Week (with number of exceptions)
8. Open the Top Offenders Template Excel file and paste the data in the All Employees Sheet. Make the following edits in the new spreadsheet:
- Delete the blank cells in Columns C and D and select Shift cells left.
- Delete the Grand Total row at the bottom.
9. Double-click the green square at the bottom of the right of the Office column to copy the office employee information. It will automatically stop copying when the next office is listed. Repeat for each office until all employees have the Office name listed next to their name.
10. Sort the Sheet alphabetically by Office and then by Exception from highest to lowest.
a. Highlight the entire sheet by clicking the arrow in the top-left of the table.
b. Click the Filter icon in any column.
c. Hover over Sort by Color and select Custom Sort.
d. The sorting should already be set up properly. If it isn't, set it up according to the image below. Click OK.
11. The All Employees tab is now complete. Copy and paste the data from this tab into the Top Offender Sheet. Sort the Exceptions column from largest to smallest. Delete the rows of employees who had 10 or fewer Exceptions.
a. For the Prior Week Exceptions column, perform a VLOOKUP using last week's report. Look up the value in column B (B2) and use columns B and C from the prior week's All Employees Sheet as the Table Array. Enter 2 for the Col_Index_num and enter False for the Range_lookup. Click OK.
b. For the Difference column, subtract the cell data in the Prior Week column from the cell data in the Exception column. The formula should be =C2-D2 in cell E2.
NOTE: Highlight cells D2 and E2 then double-click the bottom-right corner of the selected cells. This will copy each formula down their respective column.
NOTE: If the employee has a #N/A in the Prior Week and Difference columns, they had 0 Exceptions for the prior week. This could be because they are a new employee, had no visits, or completed all of their visits without exceptions. Replace and #N/A entries with 0.
12. Copy columns D and E and paste as values to remove the formulas and retain the data values.
13. Sort the sheet alphabetically by Office and then by Exceptions from highest to lowest. The Top Offenders tab is now complete. Proceed to the Prior Week Exceptions tab.
14. Go to last week's report and copy columns A and B. Paste them in this week's Prior Week Exceptions tab.
15. Perform a VLOOKUP using this week's Top Offenders tab. In cell C2, look up the value in column B (B2) and use the columns B and C from this report's Top Offenders tab as the Table Array. Enter 2 for the Col_Index_num and enter False for Range_lookup. Click OK. Double-click the bottom-right corner of the selected cells. This will copy the formula down the column.
NOTE: If the employee has a #N/A in the Top Offender of This Week? column, they were not a top offender on this week's report. Replace all #N/A entries with No and any numbers with Yes.
16. Sort the Sheet alphabetically by Office and Top Offender This Week.
17. Filter the Top Offender This Week? column to exclude the Yes entries and only display the No entries.
The Top Offenders Report is now complete. Return the Top Offenders tab and save the file with a new name to reflect the week / period.
NOTE: This week's spreadsheet will be needed to run next week's report data so VLOOKUPS are performable.
Source: Operations Support Analyst