topic Re: Past 12 months aggregated formula in App Development

Past 12 months aggregated formula

AnnaEKinch — Tue, 04 May 2021 21:02:17 GMT

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%

I don't think my R12Flag is relevent

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

Re: Past 12 months aggregated formula

Dalton_Ruer — Wed, 05 May 2021 11:49:13 GMT

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?

Re: Past 12 months aggregated formula

AnnaEKinch — Wed, 05 May 2021 19:12:18 GMT

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.