Headcount Trend Report provides an overview of the number of employees in an organization / department/ division over time periods. The periods can be Year / Quarter / Month. Such a report enables organizations to effectively monitor the employee-related information and headcount over a period in a single view.
How to create HeadCount Trend Over Years?
In People Analytics, there is no direct option (a function or filter) to create headcount trend over different time periods (Months/Quarter/Years). However, it can be achieved via calculated columns.
Let us consider a use case of creating headcount trend over different years.
Different steps involved in creating a headcount trend report over years are detailed below.
- Begin with selecting Job Information table in Query Designer. Should mandatorly include columns
- Employment#Job Information#Effective Start Date
- Employment#Job Information#Effective End Date
- Once the columns are selected, create calculated columns (measure) to indicate if an employee belong to on organization for a particular year. In the sample code snippet below, employees available to an organization last year is marked True.
IF(YEAR([Employment#Job Information#Effective Start Date]) =YEAR(CURRENTDATE())-1 and (YEAR([Employment#Job Information#Effective End Date])>YEAR(CURRENTDATE())-1 or [Employment#Job Information#Effective End Date]=[Last Day Current Year-1]), 1, 0 ) * Change the numeric value (1) in all places for previous years. For last year it is -1, one year before last year -2 etc.
TODATE(CONCAT(TOTEXT(YEAR(CURRENTDATE())-1), "/12/31"),"yyyy/mm/dd" ) * Change the number accordingly for previous years
- Similarly based on requirement calculated column to represent required years for reporting.
- Note: Change the logic in calculated columns accordingly for other years
- Next step would be to get the sum of employees which are marked as True in the calculated column to get the headcount for that specific year.
- For that, select Aggregation type as either none or sum for all the calculated columns representing years in query the designer
- Must ensure that either no time filter is applied or a data range filter which covers the required period for reporting is applied
- Navigate to Story Designer and insert any aggregated widget
- Select calculated columns in the widget
Note: For headcount trend over month / quarter, modify the calculated columns logic accordingly.
In this blog post we saw how to create a headcount trend over time periods. The steps involve creating requisite calculated columns for the time, applying aggregation and visualizing in story designer.