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 Dana,
can you please share the formula on how to calculate trend over month on month basis
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
Please use Workforce Analytics for any time trending needs for Headcount .
Using Report Story is a workaround, complex. It does not support time trending and permissions. For example, a manager will in Report Story only see data based on permissions as of today. If a manager Lucy had 20 people in her team 2 months ago but 10 has moved to another manager Joe, them Lucy will not see the 10 people as of today and also 10 people as of 2 months ago. If an admin runs the report the report will show 10 people as of today and 20 people as of 2 months ago. You cannot explain this to business users, so please Just Don't use Report Story for time trending of Headcount - unless it is for global system admins only.
Thanks,
Erik Ebert, Effective People
Hi All,
I was able to create month on month headcount trends with a slightly different approach on the formula.
This is what I did
Hope this helps.
Hi Arun,
I tried your formula as we are wanting a "rolling HC" widget that shows the HC for each month in a 12 month period. However, when I created the FDCY calculated column the report preview throws an error and when I try to inlcude FDCY into the JAN_CY calculated column, the formula is not accepted and I get a message: Formula could not be accepted: Function "AddMonthToDate" expects parameter 1 of type "Date" but receives "string". please rewrite formula.
Any guidance is appreciated
Best,
Thomas
Hi Thomas, would it be possible to share the formula you are using, i can take a look and see.
It works well for me, so it could be some syntax error or a slight difference in the way your system has been configured.
Hi Arun,
Sorry for the delayed response, was on PTO last couple days. Honestly, I just copied and pasted your formulas that you shared. When I copied the JAN_CY formula, it did not like that as it stated the formula was looking for date type but it was string type. Not sure why it wont work in our system if it is working for you?
Best!
Hey Thomas, reading the error it seems like the issue is with the FDCY formula. FDCY needs to be in a date format - can you share what you have used there. Happy to get on a call with you as well if you think that is better.
Below is the formula I used for FDCY where i converted it to a date.
TODATE(CONCAT(TOTEXT(YEAR(CURRENTDATE())), "/01/01"),"yyyy/mm/dd" )
Hey Arun,
I appreciate you willing to help me. A call next week might be great. I am using that exact formula you are for FDCY. I literally copied and pasted from your post.
TODATE ( CONCAT ( TOTEXT ( YEAR (CURRENTDATE())) , "/01/01" ) , "yyyy/mm/dd" ).
I am a little confused by the formula. TODATE takes a string and converts it to date format but the formula then appears to be converting a date field to TOTEXT? What is the "TOTEXT" and CONCAT doing specifically in this case?
Hi Thomas,
yes, i was trying to fix the dd and mm as 01/01 respectively and have only the year keep changing as the current year. Hence had to convert it to text first to concatenate them. Once they are concatenated, we can convert them back to date.
we can connect next week on a call if that works for you.
A call would be great!
Hi Erik
This is unbelievable from SAP - we don't have the WA module and won't be in the near future. The fact there is not a workaround for a simple trend headcount report from current story/table is unbelievable. It's a core HR metric.
If you can help with a workaround - please do. Even for admin is fine.
Thanks
Hi Lucy Weaver , Headcount is possible to create as a simple point in time metric. That is a widely used metric, I agree. For time trending you need to have a datawarehouse where metrics are managed with a time trend. SuccessFactors is not a datawarehouse. If you buy S/4 HANA you get an ERP system. With it you will get plenty of reporting capabilities. But if you want to report on revenue over time, you need a datawarehouse solution. Similar with any other system you might purchase, from SAP or others.
The workaround solution for admins is to use one of the provided templates from SAP for Report Story. I believe it is "Workforce Analysis" or "Headcount and FTE" that shows how to create a time trended headcount. Note this will work, but if you have tens of thousand employees it will be slow to use. And it will not allow you to use derived measures, such as "Heacount - Manager", "Headcount - Male", "Headcount - Female" and do time trending on these. For these use cases please go to Workforce Analytics.
https://help.sap.com/docs/SAP_SUCCESSFACTORS_PEOPLE_ANALYTICS/f1cbadf36a8d450fb2cb4324f9d56607/f7876e08ffd347078e5e1b149693bdd9.html?version=2011
Kind regards
Erik Ebert, Effective People