You can use Workforce Analytics to create a custom dashboard to proactively monitor the qualifications your employees hold and when they will expire (or have expired). In this article, we will look at creating a dashboard like the one below, which is a combination of three items:

• A count of each qualification held
• A count of how many qualifications expire each month
• A grid that shows all qualifications, indicating in red any that are expired and in pink those that are coming up for expiration soon

Once the dashboard is created, you will be able to filter it for specific locations, departments, and roles with the dashboard parameters:

For more general information on Workforce Analytics, check out the Workforce Analytics Overview.

Building the dashboard

In edit mode of Workforce Analytics, create a new dashboard with the data source of Employee Qualification Data.

Note: If you don't have access to edit mode, talk to your administrator. Only one person
at your organisation would need to create this report, and then it can be shared to others
as needed.

Qualification Counts

1. Select on the left or in the middle the icon to create a Card:

2. When hovering over the item, click the cog icon to start your configuration.

3. Click Add Card and select the EmployeeID field from the list.

4. After selecting the field, click on the top right where it says EmployeeId (Sum) and change the Summary Type to Count.

5. Click the back arrow at the top of the binding window, and then click on Card Layout and select Centered:

6. Then on the left click Add Series and select Qualification as the field:

7. You can rename the card by clicking on the wrench icon and updating the caption.

Expiration Counts

1. On the left, click the Card icon to add another card.

2. Follow steps 2-5 above to Add a Card for EmployeeID, switch to Count, and use the Centered Layout.

3. This time when you go to Add Series, you'll select Expiry_Date and then change the Group Interval to Month-Year.

4. Once again, use the wrench icon to update the caption for the card:

Qualification Grid

1. On the left, click on the Grid icon to add the grid to the dashboard. At this point, you can use the arrows and drag and drop the sides of the different panels to resize and organise the layout of your dashboard page. Refer to the picture at the top of the article for the recommended layout.

2. Click on the cog icon (binding) for the grid and select the columns you want to include in the grid. You can use the screenshot below as a recommendation of which columns to select. For the date fields, make sure you update the Group Interval to Day-Month-Year. You can also use the Options to update the column headings.

Note: You will need to click Add Column after each selection.

3. Now we will need to make a Calculated Field Column to calculate how many days until the Qualification expires. Add one more column, but this time click the f+ button to add a new function.

4. Give your calculated field the name Days to Expiry or similar. Copy the formula "DateDiffDay(Today(), [Expiry_Date])" into the box without the quotes. Click Save when finished.

5. For the Days to Expiry column, click on Conditional Formatting and click the + to create your first rule.

Select the Condition Type of Value, then Less Than Or Equal To:

Leave the Value at 0 and choose the red colour for the Style.

Finally go to the Miscellaneous section and turn Apply to Row to ON.

6. You can set then a second Conditional Formatting rule to highlight the qualifications expiring in the next 30 days. Just click the + again and follow the same directions, but this time select the Value Between 0 and 31 days and select the pink colour.

6. If you have qualifications that do not expire in your database, you can remove them from your grid by going to the Funnel icon and then Visible Data Filter:

In the editor, hover your cursor to the right of And until you see the +, then click and choose Add Condition. Change the field and operator to Expiry_Date Is not null. Click OK.