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.
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
- 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.
Thank you for this information. It is extremely useful.
To confirm, if we wanted current and the last 4 years, we would have a total 10 calculated columns?
Yes. That's correct.
Thank you so much for replying.
When I try to set a filter by Gender, it breaks.
Example: I am trying to visualize how many females have been promoted year or year
I recommend you reach out to customer support with details of the error. They will be able to help.
Clever solution for this report output! Bit of a shame that such as approach is necessary for a basic trend report, however.
Is this basically the same approach found in the [SF_EMP_Headcount_and_FTE_v1.0]
Is the Effective End Date field automatically populated when a new sequence is inserted?
How do we avoid counting duplicate User IDs or Person IDs for someone who has multiple Job Info records within the defined time period?
Hi Shaji,
Do you know if and when Time Based Dimension Hierarchies will be available in Stories in People Analytics, I saw it previously in the product roadmap but it's since been removed? In the guide to the chart types there's a number of tantalising examples, Line and Stacked Area Chart both feature "Time" dimensions dynamically split by Months/Years.
https://blogs.sap.com/2020/12/29/sap-successfactors-people-analytics-report-stories-data-visualization-series-selecting-the-right-chart-part-3-trend-and-distribution-charts/
There's also the drill down symbol which would be another great feature to be able to move between Years/Quarters/Months.
Kind regards,
Simon