Turnover calculation query

Abhijith · 2021-11-22T05:57:44+00:00

There was an error rendering this rich post.

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

Copy of TURNOVER FORMULA.xlsx

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.

Quick Links

Accepted answers

All comments

amehdad

Hi @Abhijith, as you already have the total 'Employed' and total 'Terminated' from the initial ETL, there's no need for another ETL.

It appears you are after rolling aggregates, so I suggest you look into these great write-ups, as a start, from @GrantSmith:

https://dojo.domo.com/discussion/52681/domo-ideas-conference-beast-modes-running-totals

https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

Abhijith

Hi @amehdad ,

Thanks for the write-ups from @GrantSmith

turnover1.docx

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.

Abhijith

Hi @amehdad ,

Thanks for the write-ups from @GrantSmith

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

turnover1.docx

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

amehdad

Before delving into your working, can I confirm that you have Window functions enabled, in your instance, from your customer success rep?

Abhijith

Hi @amehdad ,

Let me check and revert back. Thanks

amehdad

@Abhijith how did you go?

Abhijith

@amehdad

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

Abhijith

Hi @amehdad

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.

amehdad

Nice! Have you noticed any changes in values to the Monthly Rolling Turnover?

Abhijith

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.

amehdad

Hi @Abhijith, so you need two calculated fields for 'Exit' and 'Employed/Headcount'. Using the fields from the ETL, the Exit beast mode would like:

''' SUM(SUM('Terminated')) OVER (PARTITION BY YEAR('Last Day of Month'), MONTH('Last Day of Month') ORDER BY 'Last Day of Month')

You would then need to calculate the rolling average headcount for each month (year vs prior year), which I don't know how to code dynamically.

After getting those two working, you would combine to create the annual turnover rate calculation.

Abhijith

Hi @amehdad

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.?

GrantSmith

@Abhijith

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
)

Abhijith

Hi @GrantSmith

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.

{"contentType":"domo/dataflow-actions","data":[{"name":"Domo Dimensions - Calendar","id":"07583568-71c1-4868-b904-f6dda0f4e1c4","type":"LoadFromVault","gui":{"x":192,"y":384,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"d6b99a99-63c7-46f0-8e54-38045e29e710","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Select Columns","id":"4a46d8f5-9cd3-4fa8-91de-cfa071971e63","type":"SelectValues","gui":{"x":300,"y":384,"color":null,"colorSource":null},"dependsOn":["07583568-71c1-4868-b904-f6dda0f4e1c4"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":null},{"name":"d","rename":null}]},{"name":"Add Formula","id":"f01f1bab-c817-4fbc-a0ab-7e657ce6aadf","type":"ExpressionEvaluator","gui":{"x":408,"y":384,"color":null,"colorSource":null},"dependsOn":["4a46d8f5-9cd3-4fa8-91de-cfa071971e63"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Calculating the first day and last day of the month to make monthly totals easier to calculate."}],"expressions":[{"expression":"LAST_DAY(`dt`)","fieldName":"Last Day of Month","settings":null},{"expression":"DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)","fieldName":"First Day of Month","settings":null}]},{"name":"Add Constants","id":"663b8cde-daf5-4879-bece-0808c21f5ac1","type":"Constant","gui":{"x":492,"y":264,"color":null,"colorSource":null},"dependsOn":["7468a0b2-7cc8-46ef-9fc7-6c12ec70a05b"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Adding a constant to do a cross join on\n"}],"fields":[{"name":"Join Column","type":"LONG","expr":null,"value":"1"}]},{"name":"Get Unique Months","id":"108e1583-348c-49fa-a485-20ad10442bee","type":"GroupBy","gui":{"x":504,"y":384,"color":null,"colorSource":null},"dependsOn":["f01f1bab-c817-4fbc-a0ab-7e657ce6aadf"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Grouping on the first day and last day of the months to have less data to join to and improve efficiency. There's a little hackery going on here with the join column as the min(d) will always be 1 and we'll use that to do a cross join."}],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Last Day of Month"},{"name":"First Day of Month"}],"partitionedAggregation":false,"fields":[{"name":"Join Column","source":"d","type":"MIN","valuefield":null}]},{"name":"Join Data","id":"8e6dbe75-36a9-404e-9a38-58a905ddd17d","type":"MergeJoin","gui":{"x":612,"y":312,"color":null,"colorSource":null},"dependsOn":["663b8cde-daf5-4879-bece-0808c21f5ac1","108e1583-348c-49fa-a485-20ad10442bee"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Doing a giant cross join since Domo doesn't support anything other than key value joins in Magic ETL 2.0."}],"joinType":"INNER","step1":"663b8cde-daf5-4879-bece-0808c21f5ac1","step2":"108e1583-348c-49fa-a485-20ad10442bee","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[{"name":"Join Column","rename":"","remove":true}],"schemaModification2":[{"name":"Join Column","rename":"","remove":true}],"partitioningInputId":""},{"name":"Filter Rows","id":"0217d575-560b-4b1b-9c67-66e89730034d","type":"Filter","gui":{"x":720,"y":312,"color":null,"colorSource":null},"dependsOn":["8e6dbe75-36a9-404e-9a38-58a905ddd17d"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"`dt` <= `Last Day of Month` - Was this person hired before the months end?\n\n AND (`term_dt` <= `Last Day of Month` The employee was terminated prior to the end of this month\nOR `term_dt` IS NULL) -- OR this person still employed"}],"filterList":[{"expression":"`dt` <= `Last Day of Month` AND (`term_dt` >= `First Day of Month` OR `term_dt` IS NULL)","andFilterList":[]}]},{"name":"Add Formula 2","id":"18c0c25d-ab75-48b2-8ad0-44893cc1996f","type":"ExpressionEvaluator","gui":{"x":828,"y":312,"color":null,"colorSource":null},"dependsOn":["0217d575-560b-4b1b-9c67-66e89730034d"],"removeByDefault":false,"notes":[{"x1":null,"y1":null,"x2":null,"y2":null,"body":"Calculate how many were hired in a month, terminated in a given month and how many were currently employed in the given month?\n\nTerminated is set to be -1 for display purposes so you can have a diverging bar chart each month"}],"expressions":[{"expression":"CASE WHEN LAST_DAY(`dt`) = `Last Day of Month` THEN 1 ELSE 0 END ","fieldName":"Hired","settings":null},{"expression":"CASE WHEN LAST_DAY(`term_dt`) = `Last Day of Month` THEN -1 ELSE 0 END","fieldName":"Terminated","settings":null},{"expression":"CASE WHEN (`term_dt` IS NULL OR `term_dt` > `Last Day of Month`) AND `dt` <= `Last Day of Month` THEN 1 ELSE 0 END","fieldName":"Employed","settings":null},{"expression":"DATEDIFF(`term_dt`, `dt`)","fieldName":"Employment Duration","settings":null},{"expression":"CASE WHEN `Employment Duration` < 365 THEN '< 1 Year' ELSE '1+ Years' END","fieldName":"Employment Duration Bucket","settings":null}]},{"name":"Select Columns 1","id":"f27dd286-f26b-4343-821f-3071c3df207d","type":"SelectValues","gui":{"x":936,"y":312,"color":null,"colorSource":null},"dependsOn":["18c0c25d-ab75-48b2-8ad0-44893cc1996f"],"removeByDefault":false,"notes":[],"fields":[{"name":"random_number","rename":null},{"name":"dt","rename":null},{"name":"term_dt","rename":null},{"name":"First Day of Month","rename":null},{"name":"Last Day of Month","rename":null},{"name":"Hired","rename":null},{"name":"Terminated","rename":null},{"name":"Employed","rename":null},{"name":"Employment Duration","rename":null},{"name":"Employment Duration Bucket","rename":null}]},{"name":"Employment Status","id":"0d8d4d76-8844-4a30-ac74-e905ac2cc70b","type":"PublishToVault","gui":{"x":1032,"y":312,"color":null,"colorSource":null},"dependsOn":["f27dd286-f26b-4343-821f-3071c3df207d"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"34526147-5582-4cf6-abd9-27d45a364585","type":"DataFlow","name":"Employment Status","description":"https://dojo.domo.com/discussion/53740/how-to-build-card-total-headcount-based-on-the-hire-date-and-termination-date","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]},{"name":"Input DataSet","id":"7468a0b2-7cc8-46ef-9fc7-6c12ec70a05b","type":"LoadFromVault","gui":{"x":372,"y":264,"color":10827556},"dependsOn":[],"removeByDefault":false,"notes":[],"executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null}]}

GrantSmith

If you want the 12 month turnover rate you'll need more than 1 year worth of data. When it's attempting to do the lag for prior months and getting 12 prior records it's returning NULL. When you use + and NULL the end result is a NULL so your data is blank for all of the prior months. The beast mode should work assuming you have more than 12 months of data in your dataset.

other categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.