Skip to Content
Product Information
Author's profile photo Shaji Sivaraman

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.
  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 widgetSample%20Chart

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.

Assigned tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jennifer Hamilton
      Jennifer Hamilton

      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?

      1. Last Day 2021 and another for End of 2021
      2. Last Day 2020 and another for End of 2020
      3. Last Day 2019 and another for End of 2019
      4. Last Day 2018 and another for End of 2018
      5. Last Day 2017 and another for End of 2017
      Author's profile photo Shaji Sivaraman
      Shaji Sivaraman
      Blog Post Author

      Yes. That's correct.

      Author's profile photo Jennifer Hamilton
      Jennifer Hamilton

      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

      Author's profile photo Shaji Sivaraman
      Shaji Sivaraman
      Blog Post Author

      I recommend you reach out to customer support with details of the error. They will be able to help.

      Author's profile photo Mike Buono
      Mike Buono

      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?

      Author's profile photo Simon Reynolds
      Simon Reynolds

      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

      Author's profile photo Dana Mallon
      Dana Mallon

      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

      Author's profile photo Aditya Jaglan
      Aditya Jaglan

      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

      Author's profile photo IBASAPCC IBASAPCC
      IBASAPCC IBASAPCC

      Hello,

      Anyone who can provide an example of the calculated column for the Headcounts on the monthly basis ?