Turnover rate 12 months

Hi

I need to calculate the turnover rate within 12 months, the formula is:

*total employees departure filtered by period/average total employees in the last 12 month filtered by period

I have the total employees departure calculated field, but I don’t know how calculate the average of the total employees when I select a specific period.

For example if I select the period 202211 the formula is :

*total employe departure in 202211/average total employees in the last 12 month between 202110 to 202211

Please if someone can help me, I will be very grateful :slight_smile:

You can use an avgOver function.

avgOver(sum(total_employees),[year])

The one thing you will need to look at is the 12 month part to partition by.

How I would do that, would be to add the difference of months from the month selected to January and then group that to a year.

You will need to use a parameter to filter and pass into this calculated field. Let’s call this ${filter_date}

first get the month that you have selected: month_selected = extract(‘MM’,${filter_date})

group_to_year_field = truncDate(‘YYYY’,addDateTime(12-month_selected,‘MM’,{date_field}))

Then you’re avgOver could be this. avgOver(sum(total_employees),[group_to_year_field ])

Let me know if that helps!