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.
Answers
-
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
6 -
Hi @amehdad ,
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.
0 -
Hi @amehdad ,
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
1 -
Before delving into your working, can I confirm that you have Window functions enabled, in your instance, from your customer success rep?
6 -
Nice! Have you noticed any changes in values to the Monthly Rolling Turnover?
9 -
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.
0 -
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.
9 -
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
- )
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
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}]}
0 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive