Skip to main content
Announcements
Join us on Feb.12 to Discover what’s possible with embedded analytics: REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnaEKinch
Partner - Contributor III
Partner - Contributor III

Past 12 months aggregated formula

Hi!

For each period (YYYYMM) I need to see the aggregated count of "EmployeeNo" that have EndDate within the past 12 months.

I use a LeaverFlag and a R12Flag (not good with R12Flag as it only looks at today -12 months) in the formula:

count({<LeaverFlag={1},  R12_Flag={1},Year=, Period=>}DISTINCT If(Year = Year(EndDate) and Month= Month(EndDate),EmployeeNo))/Count({< R12_Flag={1}, Year=, Period=>}distinct [EmployeeNo])

But I only get the count % for the period, not for the total for the past 12 months. Each month should be over 10%

AnnaEKinch_2-1620161870159.png

 

I don't think my R12Flag is relevent 

AnnaEKinch_1-1620161675316.png

Any suggestions as how I can get a rolling 12 month aggregate count?

 

2 Replies
Dalton_Ruer
Support
Support

I'm interested in understanding your requirements a little more. 

Let's say Bob leaves the company in December 2020. 

For Janauary 2021 Bob would count as having left within the previous 12 months. 

Would he also count for every other month of 2021, or do you only count the employees 1 time? 

Or should Bob only count for having left 12 months ago and be recorded in December 2021?

AnnaEKinch
Partner - Contributor III
Partner - Contributor III
Author

Hello! Many thanks for your interest.  Bob would be counted as leaving in December, and 12 months forewards, ie counted in every month from December until November.