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.

Find more posts tagged with

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.