.png)
CAPSLO | HFSP Attendance Export
Index Information
| Report Group | Data Analysis |
|---|---|
| Report Type | Dashboard |
| Documented Version | 20241024 |
| Clarity Location | Reports > Data Analysis > Community Action Partnership of San Luis Obispo (link) |
Related Resources
How to Run Data Analysis Reports in Clarity
Overview
CAPSLO | HFSP Attendance Export is a dashboard set up to facilitate attendance management for the CAPSLO Housing-Focused Shelter Program (HFSP).
The dashboard presents two tables:
- HFSP Active Enrollments is a detail table of all active (non-exited) enrollments in the HFSP program as of the current real-world date.
- CAPSLO Attendance Detail Export is a detail table of all attendance records for the following CAPSLO shelter programs going back to 08/29/2023 [the beginning of HFSP program operations]:
- CAPSLO Prado Access Center
- CAPSLO Prado Safe Parking Blended Funding SLO Services Only
- CAPSLO Prado Shelter
- CAPSLO Prado Warming Center
CAPSLO HFSP Emergency Shelter Entry Exit
The two tables serve as source files for a dedicated Excel workbook that utilizes PowerQuery to combine the data, ultimately tabulating the number of program enrollment days for which there is a corresponding CAPSLO shelter attendance record for each HFSP enrollment.
Excel PowerQuery Workflow
Power Query Configuration
PowerQuery workflows allow an Excel workbook to pull information from other files, perform advanced calculations, and synthesize new data sets. Source files are stored in a fixed location so that the PowerQuery workflow can be repeated with new exports of the source data without having to alter the underlying programming of the workflow. In this case, the Excel PowerQuery-enabled workbook is titled "HFSP Attendance Compiler," and its referenced source files are stored in a folder titled "input":
When preparing to refresh the HFSP Attendance Compiler's data set for the first time, the user will need to map the PowerQuery source queries to their local file directory. For assistance with this, please reach out to ss_hmissupport@co.slo.ca.us. Once this setup step is completed, it should not be necessary to repeat it unless there are modifications to either the file storage locations/names or the Looker reports used to generate the data.
HFSP Attendance Compiler Refresh
Staff should follow these steps to update the data tables presented within the HFSP Attendance Compiler workbook:
Navigate to the dashboard in Clarity. It should automatically run for the current real-world date, though the "Report Date" displayed in the HFSP Active Enrollments will be dated for yesterday's date.
Hover over the HFSP Active Enrollments table and then click the three dots icon at the top-right of the tile (white background) to open the action menu and select "Download data":
Before hitting "Download" on the pop-up that appears, expand the Advanced data options sub-menu and set the Number of rows and columns to include setting to "All results". Otherwise, leave all other settings at their default values.
If the file automatically saves to your "Downloads" folder, first open the saved file in Excel and click "Save As..." and then "Browse" from the File menu before continuing to the next steps.
Navigate to the folder used to locally store the HFSP Attendance Compiler and it's associated "input" folder. Then click into the "input" folder and overwrite the existing HSFP Active Enrollments Excel csv file.
Repeat steps #2-5 for the CAPSLO Attendance Detail Export table on the dashboard, saving over the correspondingly named file in the "input" folder in the user's local file directory.
Once both source files have been updated, open the HFSP Attendance Compiler workbook. To update the tables that appear on the summary and detail tabs of the workbook, navigate to the "Data" tab of the Excel toolbar and then click the "Refresh All" toolbar icon:
After a few seconds, this will update the tables to reflect the data exported from Clarity. The user can confirm that the data has been updated by observing whether the value in the "Report Date" column of the HFSP Attendance Summary tab matches what is showing on the Clarity dashboard's HFSP Active Enrollments table.
