Creating Headcount Trends by Different Time Periods on Report- Stories

Introduction

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.

1. Last Day Current Year is another calculated column with the formula

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

HeadCount Trend

Note: For headcount trend over month / quarter, modify the calculated columns logic accordingly.

Conclusion

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.