Creating Headcount Trends by Different Time Periods on Report- Stories
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.
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.