Turnover rate 12 months

Question & Answer
, ,
2

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!