cancel
Showing results for 
Search instead for 
Did you mean: 

Trailing 12 Month (TTM) Attrition %

sachin1026
Discoverer
0 Kudos

Hi All,

 

We are trying to derive Trailing 12 Month (TTM) Attrition %.

Requirement : As on any particular day, the system should look back 12 month from the previous month.


Example: On Effective Date

From 1 Mar 2024 - 31 Mar 2024:

Closing Headcount = 2500

Employee left in last 12 month (Employee Left based on Termination Date between 1 Feb 2023- 29 Feb 2024.) = 500

Employee Hired in last 12 month (Employee Left based on Hire Date between 1 Feb 2023- 29 Feb 2024.) = 200

Opening Headcount = 2800 (Closing HC - Hired + Terminated)
In this case we have created a Data model level Canculation to identify Date Difference between Effective Date and Termination/Hire Date.
The issue is occuring because we get difference in Day, Month or Year.
We are not able to make this difference show in the required manner. 

When we take Month as Granularity, then we are getting the records from 2 Jan 2023 to 31 Jan additionally as the difference is in integer so untill 13 months are completed the system is considering 12 an dreturing records.

The expectation is to create measure to calculate users separated or terminated in last 12 months.
However, when we created a measure at model level using "Month" as granularity the users with termination more than 12 months but less than 13 months are coming in the data.

The result of date difference cannot be used as decimal to apply condition for that.
So as the next option we are looking to derive this calculation based on Days granularity.
But in this case we put condition for 365 Days = 12 months

But in this case the Days Range should end on the last day of previous month.

 

View Entire Topic
AashishCherian
Advisor
Advisor
0 Kudos

Hi @sachin1026 ,

Thanks for taking the time to explain the use case, however I am still a little confused. 
You can check out the following blogs to see if this addresses your concerns:

  1. https://community.sap.com/t5/technology-blogs-by-members/date-calculations-in-sap-analytics-cloud-us...
  2. https://community.sap.com/t5/technology-blogs-by-sap/last-x-months-trend-for-a-given-month/ba-p/1342...

If these don't help, request you to share some screenshots to explain.