Creating Headcount Trends by Different Time Periods on Report- Stories

[former_member25101]

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?

SAP Successfactors Workforce Analytics (WFA) provides time-trended analysis. All metrics in WFA can be easily sliced by the available dimensions - including the time hierarchy (year/quarter/month). Therefore, the general advice is to consider WFA when you have a need for analyzing metrics across time.

However, for simple reports where you just need headcount trend over a few (specific) time periods (Months/Quarter/Years), there is a way to create such report within report stories. 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.