These fields include the Date Filter, Date Filter Start Date, and Date Filter End Date. Unlike the Reporting Period Date Filter, the new Date Filter is not linked to any fields on the database, and by itself, it in no way limits the results of a dataset.
One might ask, if the fields are not connected back to the database, then how are they used and what is their purpose? The Date Filter was designed and implemented with the goal of expanding the options for dashboard filters. Dashboard filters cannot be built around custom dimensions, so there is no way to apply more advanced logic that may include multiple dimensions into a single dashboard filter. The new Date Filter gives users a new dimension that can be utilized as a variable time filter without altering the results of the dataset on its own. Within the filtered upon Look, users can reference the Date Filter Start Date and Date Filter End Date when configuring more advanced logic that needs to be analyzed over variable time ranges. One thing to note, the Date Filter must always have a start and an end date in order to work properly. For this reason, the “before/after” a specific date filters will not work; however, the “Is in the last” date filters do work as they generate their own end dates. A few use case scenarios are summarized below.
Fields
Date Filter fields are available in all models except the Data Import Model. The fields include one filter only field and two dimensions with various date intervals for start and end date.
Sample Looks
Bed and Unit Inventory - Active Bed Inventory Look
In order to query the active bed inventory over a time period or even on a specific date, a user has to utilize a custom filter built around the bed inventory start and end dates. This can be configured around the variable reporting period filter in the HMIS Performance Model, but it is then limited to programs with an enrollment during the analyzed time period due to the underlying logic of the HMIS Performance model. Pulling all active inventories across a community is best accomplished through the Project Descriptor Model. Typically, this would be accomplished using a custom filter as pictured in the example below that pulls all bed inventories that have been active year to date.
Original Method - Active Bed Inventory Look
Dimensions
- Programs
- Name
- Bed and Unit Inventory
- ID
- Household Type
- Date - Start Date
- Date - End Date
Measures
- Bed and Unit Inventory
- Total Bed Inventory - Bed Allocations
Custom Filter
${program_inventory.start_date}<now()
AND (${program_inventory.end_date}>=trunc_years(now())
OR is_null(${program_inventory.end_date}))
Sample Look: Original method for pulling Bed Inventory prior to the release of the Date Filter fields
With the new Date Filter, we can update that custom filter logic to reference a single, filterable date range. This will allow the Date Filter to be configured as a dashboard filter, so the active bed inventories can now be analyzed over any time range with a single Look.
New Method with Date Filter - Active Bed Inventory Look
Dimensions
- Programs
- Name
- Bed and Unit Inventory
- ID
- Household Type
- Date - Start Date
- Date - End Date
Measures
- Bed and Unit Inventory
- Total Bed Inventory - Bed Allocations
Filters
- Date Filter
- Select the doner icon next to the Date Filter field to filter
- Date - Date Filter Start Date
- Date - Date Filter End Date
Custom Filter
${program_inventory.start_date}<${date_filter.date_filter_end_date}
AND (${program_inventory.end_date}>=${date_filter.date_filter_start_date}
OR is_null(${program_inventory.end_date}))
Sample Look: New Method for pulling Bed Inventory with Date Filter fields
Community Queue - On Queue Within Analysis Period Look
To identify the clients or referrals on a community queue at any point over a time range, a custom filter that references the dates that add and remove a client from the queue are needed. Since multiple date fields need to be included in the custom filter logic, there was no way to analyze a variable time range through a dashboard filter until we developed the new Date Filter.
Original Method - On Queue Within Analysis Period Look
Dimensions
- Clients
- Unique Identifier
- Referrals
- Name • Referral Community Queues
- ID
- Status
- Date • Added Date
- Is referral currently on queue?
- Date • Date Accepted In Program Date
- Date • End Date Date
Custom Filter
${referrals.added_date}<now()
AND (${referrals.Currently_On_Queue}="Yes" OR coalesce(${referrals.date_accepted_in_program_date},${referrals.end_date_date})>trunc_years(now()))
Sample Look: Original method for pulling CQ - Referred Within Analysis Period prior to the release of the Date Filter fields
New Method with Date Filter - On Queue Within Analysis Period Look
Dimensions
- Clients
- Unique Identifier
- Referrals
- Name • Referral Community Queues
- ID
- Status
- Date • Added Date
- Is referral currently on queue?
- Date • Date Accepted In Program Date
- Date • End Date Date
- Filters
- Date Filter
- Select the doner icon next to the Date Filter field to filter
- Date • Date Filter Start Date
- Date • Date Filter End Date
Custom Filter
${referrals.added_date}<${date_filter.date_filter_end_date}
AND (${referrals.Currently_On_Queue}="Yes" OR coalesce(${referrals.date_accepted_in_program_date},${referrals.end_date_date})>${date_filter.date_filter_start_date})
Sample Look: New method for pulling CQ - Referred Within Analysis Period with the release of the Date Filter fields
By Name List - Inflow/Outflow Look
By Name Lists (BNL) with associated inflow and outflow metrics have grown in popularity. Every BNL request comes with unique factors, many of which are fairly complex; however, almost all of them need to identify clients that are newly entering Category 1- Literal Homelessness. Not all enrollments in the system would classify a client as actively homeless, so only homeless response system project types need to be analyzed (ES, SO, TH, PH, & RRH). For this reason, filtering for the client’s first enrollment in the system would not identify those entering the homeless population, and filtering for the client’s first enrollment per project type will return results for each individual homeless response system project type and not the group of them as a whole. This leaves us with the first enrollment filter dependent, which can identify the first homeless response system enrollment for all clients within the relevant population. That list can very quickly reach the 5000 row limit when it is typically only desired to identify those newly entering homelessness within a specific time frame such as last month. If you filter for project start dates being within the desired time frame, then the filter dependent field will return the client’s first homeless response system enrollment within that time period (i.e. last month), not their first-ever homeless response enrollment that just happens to be within the desired time period. Therefore, due to Looker limitations, it can be very difficult to identify a group of clients who had their first-ever homeless response system enrollment during a specific time period.
This now changes with the addition of the Date Filter. Users can filter the Date Filter to a specific time range and then use custom measures to count the number of enrollments before and the number of enrollments during the analyzed period. Those custom measures can then be filtered to equal 0 and greater than 0 respectively, and voila, we have identified those clients that have newly entered the homeless response system during a specified analysis period.
Dimensions
- Clients
- Unique Identifier
Measures
- Enrollments
- First Enrollment Date
Custom Fields
- Custom Measures
-
- Enrollments Before the Analysis Period
- Enrollments Before the Analysis Period
${enrollments.start_date}<${date_filter.date_filter_start_date}
-
- Enrollments During the Analysis Period
- Enrollments During the Analysis Period
${enrollments.start_date}>=${date_filter.date_filter_start_date} AND ${enrollments.start_date}<${date_filter.date_filter_end_date}
Filters
- Enrollments Reporting Period - is any time
- Date Filter - Specific time range for analysis
- Project Type Code - SO, ES, TH, and all PH programs
- Enrollments Before the Analysis Period - is equal to 0
- Enrollments During the Analysis Period - is greater than 0
Sample Look: By Name List - Inflow Look
Updated: 04/08/2022