Skip to Content
Product Information

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?

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.
  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.


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.

You must be Logged on to comment or reply to a post.
  • 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
  • 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.

    There's also the drill down symbol which would be another great feature to be able to move between Years/Quarters/Months.

    Kind regards,