Creation of a Waterfall Report to display the Headcount Turnover

[FrankErle]

Summary

The creation of a “Turnover Report” which shows not only the “Historic Headcount” and the “Headcount Today” but also (1) the “Hires/Rehires” and “Terminations” and (2) even the “Transfer Ins” and “Transfer Outs” to a particular organizational unit is described in this blog post. It is even possible to have a dynamic selection possibility of the number of years for which you look back. This blog post provides a step-by-step description how such a report can be created.

Introduction

Figure 1 and 2 show a classical waterfall diagram of figures like the “Historic Headcount” and the “Headcount Today” according to a specific Legal Entity. For a precise balance it is required not only to consider the “Hires/Rehires” and “Terminations” but also the “Transfer Ins” to and the “Transfer Outs” of an organization unit like a Legal Entity. The balance must be:

“Historic Headcount” + “Hires/Rehires” + “Transfer Ins” - “Transfer Outs” - “Terminations” = “Headcount Today”

While the balance for a particular Legal Entity in Figure 1 is for looking back 2 years, the balance shown in Figure 2 shows the values when looking 8 years back.

Figure 1 – Waterfall diagram when looking back for 2 years

Figure 2 – Waterfall diagram when looking back for 8 years

 

In the following the technical design of the data source and of the story is described. The description covers the balance for Legal Entities. However, with minimal effort it is possible to adjust the report for other foundation object entities e.g. like location or cost center. Furthermore, it's also possible not to consider the full years (i.e. "headcount 3 years back") but also other time spans as e.g. "months" (e.g. "headcount 7 months back"). Also this is possible to achieve by minor adjustments of the report.

Technical design of the data source

The data source was designed in that way that 3 instances of the “Job Information” are joined without any time filter. Figure 3 shows the data source, including the time filter which was applied.

Figure 3 – Data source including the time filters that have been applied

When joining multiple instances of one object a so-called “Cartesian product” is created. E.g. if the job information history has 10 time slices for one employee, then you would create 10 x 10 x 10 = 1000 records in the query. The purpose of the query is the creation of the previous and subsequent record. This is required to determine internal transfers of employees between organization units since in general it is not possible to identify such internal transfers with events and/or event reasons. For example it is possible that internal transfers are posted with a "data change". Therefore it is mandatory to compare the organization unit of the actual record and the previous record to determine a "Transfer In" (comparing the actual record with a subsequent record to determine a "Transfer Out", respectively). To get rid of the duplicates due to the “Cartesian product” it is required to apply a set of smart filters. For these filters it is required to create a few calculated columns:

SEQUENCECHECKSUB - [Employment#Job Information (Copy)#Transaction Sequence Number]-[Employment#Job Information#Transaction Sequence Number]

SEQUENCECHECKPREV - [Employment#Job Information#Transaction Sequence Number]-[Employment#Job Information (Copy 2)#Transaction Sequence Number]

DATECHECKSUB - DAYS_BETWEEN([Employment#Job Information#Effective End Date],[Employment#Job Information (Copy)#Sub Eff Start Date])

DATECHECKPREV - DAYS_BETWEEN([Employment#Job Information (Copy 2)#Prev Eff End Date],[Employment#Job Information#Effective Start Date])

CHANGEFILTERSUB - IF([DATECHECKSUB] = 1 and [Employment#Job Information#Effective Latest Change] = "Y" , "Valid Date Diff",IF([DATECHECKSUB]=0 and [SEQUENCECHECKSUB]=1 ,"Valid Seq Diff" , "Invalid") )

CHANGEFILTERPREV - IF([DATECHECKPREV]=1 and [Employment#Job Information#Transaction Sequence Number] =1, "Valid Date Diff", IF([DATECHECKPREV]=0 and [SEQUENCECHECKPREV]=1, "Valid Seq Diff", "Invalid" ) )

Furthermore, additional calculated columns are needed to determine the number of hires/rehires, terminations and transfers:

HIRES - IF( [Foundation Objects#Event Reason#Event]="internal picklist ID for Hire"or  [Foundation Objects#Event Reason#Event]="<internal picklist ID for Rehire>",1 ,0 )

TERMINATIONS - IF( [Foundation Objects#Event Reason#Event]="internal picklist ID for Termination",1 ,0 )

LETRANSFERIN - IF([Job Information#FoundationObjects#Legal Entity#Legal Entity]!=[Job Information (Copy 2)#FoundationObjects#Legal Entity#Prev Legal Entity] ,1 ,0 )

LETRANSFEROUT - IF([Job Information#FoundationObjects#Legal Entity#Legal Entity]!= [Job Information (Copy)#FoundationObjects#Legal Entity#Sub Legal Entity]  ,1 ,0 )

It might make sense to apply a filter for each of the 3 instances of the job information for “Assignment Type= equal to “ST”.

Furthermore, if multiple employments are used in the system, it’s required to add a global query filter for (1) User of “Job Information” = User of “Job Information (Copy)” and (2) User of “Job Information” = User of “Job Information (Copy2)”.

Finally select a number of fields of the data source, such as “First/Last name” of the employee, “Legal entity name”, Effective Start/End Date” of all three “Job Information” instances.

Technical design of the story

A filter needs to be applied to avoid duplicates caused by the “Cartesian Product”. The red box in Figure 4 illustrates the filter conditions required to filter for the previous record, the violet box illustrates the filters required to get the initial hire and/or rehire (i.e. the record which does not have a previous record), the yellow box illustrates the filters required for the last record with effective end date = “31. Dec. 9999” (i.e. to filter for the very last record which does not have a subsequent record) and the green box illustrates the filter condition required for the subsequent record.

Figure 4 – Global filter that needs to be applied in the story

 

A calculation input control needs to be created to allow to possibility to enter for how many years the  time range of the waterfall chart should be created.

Figure 5 – Calculation Input Control that needs to be created in the story

 

Here now a description of the multiple story calculations which needs to be created of the various types. Following 6 calculated measures of type “Date Difference” are needed:

1. 
   
   
   
   1. End Date -> Today (in Days): Result Granularity “Day” with Time (A) as “Effective End Date” and Time (B) as “Current Date”
   
   
   2. End Date -> Today (in Years): Result Granularity “Year” with Time (A) as “Effective End Date” and Time (B) as “Current Date”
   
   
   3. Start Date -> Today (in Days): Result Granularity “Day” with Time (A) as “Effective Start Date” and Time (B) as “Current Date"
   
   
   4. Start Date -> Today (in Years): Result Granularity “Year” with Time (A) as “Effective Start Date” and Time (B) as “Current Date"
   
   
   5. Sub Start Date -> Today (in Days): Result Granularity “Day” with Time (A) as “Subsequent Effective Start Date” and Time (B) as “Current Date"
   
   
   6. Sub Start Date -> Today (in Years): Result Granularity “Year” with Time (A) as “Subsequent Effective Start Date” and Time (B) as “Current Date"
   
   
   
   


2. Following 8 calculated Measures of type “Calculated Measure” are needed:
   
   
   
   1. Time Slice Counter (Today) helper: IF([#Start Date -> Today (in Days)]<0 and [#End Date -> Today (in Days)]>0 ,1 ,0 )
   
   
   2. Time Slice Counter (Years Back) helper (see also Figure 6 below): IF([#Start Date -> Today (in Years)]<=-[@Looking Back (in Years)] and [#End Date -> Today (in Years)]>=(-[@Looking Back (in Years)]+1) ,1 ,0 )
   
   
   3. Time Range Counter (see also Figure 7 below): IF([#Start Date -> Today (in Years)]>=-([@Looking Back (in Years)]-1) and [#Start Date -> Today (in Days)]<0 , 1, 0)
   
   
   4. Time Range Counter (Transfer Out): IF([#Sub Start Date -> Today (in Years)]>=-([@Looking Back (in Years)]-1) and [#Sub Start Date -> Today (in Days)]<0 , 1, 0)
   
   
   5. Hires 2: ["Personal Information":HIRES]* [#Time Range Counter]
   
   
   6. Terminations 2: -["Personal Information":TERMINATIONS]*[#Time Range Counter]
   
   
   7. Transfer In 2: ["Personal Information":LETRANSFERIN]*[#Time Range Counter]
   
   
   8. Transfer Out 2: - ["Personal Information":LETRANSFEROUT] * [#Time Range Counter (Transfer Out)]
   
   
   
   

Figure 6 – Calculated Measure “Time Slice Counter (Years Back) helper” which uses “Calculation Input Control”

Figure 7 – Calculated Measure “Time Range Counter” which uses “Calculation Input Control”

 

1. Following two calculated Measures of type “Restricted Measure” need to be created:
   
   
   
   1. Time Slice Counter (Today) which uses “Time Slice Counter (Today) helper” as Measure and Restriction for “Employment status” not equal to “Retired/Terminated”
   
   
   2. Time Slice Counter (Years Back) which uses “Time Slice Counter (Years Back) helper” as Measure and Restriction for “Employment status” not equal to “Retired/Terminated”
   
   
   
   


2. Following calculated Measures of type “Aggregation” are needed:
   
   
   
   1. Headcount Today which uses “Time Slice Counter (Today)” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
   
   
   2. Hires/Rehires which uses “Hires 2” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
   
   
   3. Termination which uses “Terminations 2” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
   
   
   4. Transfers In which uses “Terminations 2” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
   
   
   5. Transfers Out which uses “Transfer Out 2” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
   
   
   6. Historic Headcount which uses “Time Slice Counter (Years Back)” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date”, “Event” and “Effective Latest Change” are used.
   
   
   
   

 

With all of that it’s now easily possible to create the chart itself. The chart type “Comparison” -> "Waterfall" needs to be used with the 6 measures from step (6) above. Finally, the “Legal Entity” has to be used as the dimension.

Of course, it is also possible to use other foundation object entities instead of the Legal Entity as e.g. location, country, cost center or department. If this is needed, it is required to adjust (1) the calculated columns LETRANSFERIN and LETRANSFEROUT and (2) the dimension in the waterfall chart by replacing the legal entity by the requested foundation object.