Product Information
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?
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
- 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
For headcount trend over month / quarter, modify the calculated columns logic accordingly. - would you have an example for a formula to create monthly calculations? I tried to use the above formula but it does not work as months have different numbers of days in total.
Kind Regards
Dana
Hi Shaji Sivaraman, Thank you very much for the very insightful blog. It really helps.
Apparently, we are trying to get the Headcounts on the monthly basis, and I can't seem to get the formula working for it. I have been trying the below example,
TODATE(CONCAT(TOTEXT(YEAR(CURRENTDATE())),CONCAT(TOTEXT(MONTH(CURRENTDATE())-1),"//28")),"yyyy/mm/dd")
The formula appears correct, However, the query is not progressing or giving any output for the field created.
Can you please advise or share the correct calculations to calculate the HC on a monthly basis?
Many Thanks in advance,
Aditya
Hello,
Anyone who can provide an example of the calculated column for the Headcounts on the monthly basis ?
Did anyone get the formula right for headcount per month and per year?
I'm having difficulty adding "-1" to my Last Day Current Year calculated column in the main formula. The last part of the formula in the blog reads:
or[Employment#Job Information#Effective EndDate]=[Last Day CurrentYear-1]),1,0)
When I type in the custom column description text for Last Day Current Year, the column id shows as A1 (for example). I can then bring that calculated column into my formula but anytime I add anything to do with -1, it errors out. See italics.
or [Employment#Job Information#Effective End Date]= [A1]-1),1 ,0 )
Can anyone help? Thanks, John