# Turnover calculation query

The assistance from the community was great and I have been exploring Domo. This request is in continuation with this data set https://dojo.domo.com/discussion/comment/55564#Comment_55564

Based on the turnover calculation in the excel file, I would like to get some guidance on whether it can be done on the beast mode or does it require another ETL process. I would really appreciate how I could incorporate these excel formulas into Domo for every month's turnover calculation to display the using the 24 months data. Thanks in advance.

• ⚪️
edited November 2021

Thanks for the write-ups from @GrantSmith

For some reason, I'm not able to view the cumulative effect as per the windows function. My intention is to get the grand total of all the 'Terminated' for these months so that I can use them in the turnover formula. Please advise if I'm going in the right direction.

• ⚪️
edited November 2021

Thanks for the write-ups from @GrantSmith

Based on that I tried to work it out and I'm not winning.

My basic objective is to get the total sum of 'terminated' for that period(it is always for 12 months) so that I can use it in my calculation.

Kindly advise what might be wrong. Thanks

• Hi @amehdad ,

Let me check and revert back. Thanks

• I'm still waiting on the feedback from Domo support. They have asked me to wait. I'll revert back to you once I received confirmation from their end. Thanks

They have now confirmed that the windows function has been enabled. Can you please check and advise how do I take it forward. Thanks once again.

• I needed direction on how to work out the formula for the calculation in the excel file attached at the beginning of the thread. Especially how do I identify the average of the first and last month for each month's calculation.

I got those calculated fields for 'Exit' and 'Employed' but yeah I was stuck at the part where to generate dyamically.

Also is there any way I can sum up all the 'exits' for a period(say 12 months fixed) and then I can use it to work out the turnover rate calculation.?

• Indiana 🥷
edited December 2021

You should be able to use multiple lag functions to calculate the total and average. This is assuming your data has been unpivoted and you have a single row for each month instead of a single column for each month as in your excel document.

Part 1:

(LAG(SUM(`Exits in month`), 12) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 11) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 10) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 9) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 8) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 7) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 6) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 5) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 4) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 3) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 2) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 1) OVER (ORDER BY `headcount as at date`)

Part 2:

(LAG(SUM(`Employed`), 12) OVER (ORDER BY `headcount as at date`) + SUM(`Employed`)) / 2

Turnover Rate:

(
(LAG(SUM(`Exits in month`), 12) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 11) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 10) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 9) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 8) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 7) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 6) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 5) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 4) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 3) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 2) OVER (ORDER BY `headcount as at date`) +
(LAG(SUM(`Exits in month`), 1) OVER (ORDER BY `headcount as at date`)
)
/
(
(LAG(SUM(`Employed`), 12) OVER (ORDER BY `headcount as at date`) + SUM(`Employed`)) / 2
)

**Did this solve your problem? Accept it as a solution!**
• Thanks for the suggestion. Based on your feedback I managed to find the turnover % for a month. But now if I want to find the turnover% for the other months, is it possible to do that in the same beast mode or do I need to create a separate beast mode?

I'm using the bogus dataset shared earlier.