Rolling 12 month avg by Branch and Employee

JR_S1L
JR_S1L ⚪️
edited January 4 in Beastmode & Analytics

I need to calculate the rolling 12 month average of units per branch location divided by number of employees in that branch. I have this calculation for rolling 12 month average by branch:

(

 lag(SUM(`TotalUnits`)) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 2) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 3) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 4) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 5) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 6) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 7) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 8) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) + 

 lag(SUM(`TotalUnits`), 9) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 10) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 11) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)) +

 lag(SUM(`TotalUnits`), 12) OVER(PARTITION BY `BranchName` ORDER BY YEAR(`FundedDate`), MONTH(`FundedDate`)))

/ 12


How would I then divide it by the number of employees per branch?

Thanks in advance!!

Tagged:

Categories